Moving data from one table to 3

Question

Moving data from one table to 3

sc-w

Hall of Fame

Points: 3664
More actions
April 7, 2009 at 6:49 am

#155522

Hi,
I am in a situation where I need to move data from one table into 3 other tables in the same database(SQL Server 2005). What would like to find out is the best way to achieve this. It will need to be done once a day and then the data from the source table will need to be deleted.
I have been think of a stored procedure but am not sure exactly on the syntax so am yet to get that working. Before I take that route I thought some advice was needed.
Any help would be greatly appreciated. Thanks in advance.
Here is the test procedure I setup trying to move one field from a table to a field in another table. I did this just to test my code and the logic.
Here is the error:
Msg 201, Level 16, State 4, Procedure move_ebs, Line 0
Procedure or function 'move_ebs' expects parameter '@forename', which was not supplied.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[move_ebs]
@forename varchar(50)
AS
SET @forename = (select forename from wce_ilr where learning_aim = '10003265')
INSERT INTO inserttest (forename) VALUES (@forename)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

Depending on the quantity of data you may want to look at SSIS. You can read the source data and use a multicast component to create multiple feeds of the same data and send to three seperate output destinations.

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 2

Hi

To your error:

Seems that you call your procedure without the @forename parameter.

To your problem:

Some more information would be helpful:

* Are the 4 (source + 3 destinations) same structure?

* Could you please provide the DDL?

* Could you please provide some sample data? If know how have a look to my signature.

* How do you determine the destination table?

* Can the data change (needs to be updated)? Can they change since loading into destinations?

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

sc-w Hall of Fame Points: 3664 More actions · Answer 3

Thanks for your replies. In answer to your questions see below:

* Are the 4 (source + 3 destinations) same structure?

--The destination tables have the same field names.

* Could you please provide the DDL?

-- Not to clear on what you need here. Here is a small sample, these are some field names in the source table (wce_ilr): learner_name, learner_surname, company_name, company_contact, course_name, course_title, start_date

The tables i want one row of the source(wce_ilr) table to populate

destination table 1 - wce_contact fields(learner_name, learner_surname) ----- source table fields (learner_name, learner_surname)

destination table 2 - wce_contact fields(company_name, company_contact) ----- source table fields (company_name, company_contact)

destination table 3 - wce_contact fields(course_name, course_title, start_date) ----- source table fields (course_name, course_title, start_date)

Hope this is clear. destination table 1 and 2 arethe same table but one row from the source table will need to create two seperate entries in the wce_contact table. the destination table 3 will only ever need one entry.

* Could you please provide some sample data? If know how have a look to my signature.

-- To be honest the table is huge, is it possible from what i mention above you can get the logic. If i get an idea based on those fields above i should be able to work out the rest.

* How do you determine the destination table?

-- I know what fields need to go in what tables already, this i have drawn out.

* Can the data change (needs to be updated)? Can they change since loading into destinations?

-- once the data is inserted there will not need to be any updates. I might need to add this later on down the line.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

sward (4/8/2009)
Thanks for your replies. In answer to your questions see below:
* Are the 4 (source + 3 destinations) same structure?
--The destination tables have the same field names.
* Could you please provide the DDL?
-- Not to clear on what you need here. Here is a small sample, these are some field names in the source table (wce_ilr): learner_name, learner_surname, company_name, company_contact, course_name, course_title, start_date
The tables i want one row of the source(wce_ilr) table to populate
destination table 1 - wce_contact fields(learner_name, learner_surname) ----- source table fields (learner_name, learner_surname)
destination table 2 - wce_contact fields(company_name, company_contact) ----- source table fields (company_name, company_contact)
destination table 3 - wce_contact fields(course_name, course_title, start_date) ----- source table fields (course_name, course_title, start_date)
Hope this is clear. destination table 1 and 2 arethe same table but one row from the source table will need to create two seperate entries in the wce_contact table. the destination table 3 will only ever need one entry.
* Could you please provide some sample data? If know how have a look to my signature.
-- To be honest the table is huge, is it possible from what i mention above you can get the logic. If i get an idea based on those fields above i should be able to work out the rest.
* How do you determine the destination table?
-- I know what fields need to go in what tables already, this i have drawn out.
* Can the data change (needs to be updated)? Can they change since loading into destinations?
-- once the data is inserted there will not need to be any updates. I might need to add this later on down the line.

Sample data does not mean all the data, just a sample like 10 rows or so.

What you were asked for is the CREATE TABLE statements for the tables (DDL, or Data Definition Language statements).

Please read the first article i have linked below in my signature block regarding asking for assistance. The article, written by Jeff moden, provides very clear instructions on what you need to to do to answer the request given to you earlier.

Following the guidelines in that article, you will get tested and verified code to assist you in resolving your problem.

sc-w Hall of Fame Points: 3664 More actions · Answer 5

Hi,

Ok, it's not that straight forward or easy to explain so i have taken a few fields just to get a proof of concept. So the wce_contact table is getting a row created for the learner details and a seperate row for the company details, then the wce_delegate_course_link table takes the course info. All of this is produced from one row in the surce table.

Please let me know if it makes sense. I am about to drive home but can respond in about an hour to any questions.

Thanks for the help

Source:

USE [wce_snc]

GO

/****** Object: Table [dbo].[wce_contact] Script Date: 04/08/2009 16:50:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[wce_ilr](

[COMPANY_Contact] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[company_name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Learner_Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Learner_Surname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[course_name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[course_title] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[start_date] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[UNIQUEID] [varchar](16) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [pk_wce_contact_uid] PRIMARY KEY CLUSTERED

(

[UNIQUEID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

destination table 1 / 2 for company and learner but rows would need to be created from one row in the source table.

USE [wce_snc]

GO

/****** Object: Table [dbo].[wce_contact] Script Date: 04/08/2009 16:50:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[wce_contact](

[COMPANY_Contact] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[company_name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Learner_Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Learner_Surname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[UNIQUEID] [varchar](16) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [pk_wce_contact_uid] PRIMARY KEY CLUSTERED

(

[UNIQUEID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

destination table 3 for course info

USE [wce_snc]

GO

/****** Object: Table [dbo].[wce_contact] Script Date: 04/08/2009 16:50:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[wce_delegate_course_link](

[course_name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[course_title] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[start_date] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[UNIQUEID] [varchar](16) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [pk_wce_contact_uid] PRIMARY KEY CLUSTERED

(

[UNIQUEID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

Sample data, expected results based on the sample data? Would be of great value.

sc-w Hall of Fame Points: 3664 More actions · Answer 7

Is this ok, the data example is quite simple as it would only be in the source table, then from there the query will push that data into the destination tables.

the destination would get an auto number generated for the uniqueid for each row added. The data would look something like this:

INSERT INTO wce_ilr(uniqueid, COMPANY_Contact, company_name, Learner_Name, Learner_Surname, course_name, course_title,start_date)

SELECT '1','john Smith','Tesco','paul','king','making rods','rods',10/01/2009' UNION ALL

SELECT '2','james olla','Sainsburies','paul','king','making cars','cars',11/01/2009' UNION ALL

SELECT '3','fred hick','kwik fit','paul','king','making bikes','bikes',12/01/2009' UNION ALL

SELECT '4','mark peters','coop','paul','king','making trains','trains',13/01/2009' UNION ALL

SELECT '5','sam roberts','mistral','paul','king','making planes','planes',14/01/2009' UNION ALL

SELECT '6','peter jones','baitmans','paul','king','making shoes','shoes',15/01/2009'

And the end result from the first row above would look like:

wce_contact

uniqueid COMPANY_Contact Company_name

1 John Smith Tesco

wce_contact

unqiueid Learner_name learner_surname

2 Paul King

wce_delegate_course_link

uniqueid course_name course_title start_date

1 Making Rods rods 10/01/2009

------------------------------

Hope this helps. Let me know. Thanks

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 8

Hi sward

Thanks for DDL and sample data ;-).

Try this:

DECLARE @wce_ilr TABLE (

[COMPANY_Contact] [varchar](50) NULL,

[company_name] [varchar](50) NULL,

[Learner_Name] [varchar](50) NULL,

[Learner_Surname] [varchar](50) NULL,

[course_name] [varchar](50) NULL,

[course_title] [varchar](50) NULL,

[start_date] [varchar](50) NULL,

[UNIQUEID] [varchar](16) NOT NULL,

PRIMARY KEY CLUSTERED ([UNIQUEID] ASC)

)

DECLARE @wce_contact TABLE (

[COMPANY_Contact] [varchar](50) NULL,

[company_name] [varchar](50) NULL,

[Learner_Name] [varchar](50) NULL,

[Learner_Surname] [varchar](50) NULL,

[UNIQUEID] [varchar](16) NOT NULL,

PRIMARY KEY CLUSTERED ([UNIQUEID] ASC)

)

DECLARE @wce_delegate_course_link TABLE (

[course_name] [varchar](50) NULL,

[course_title] [varchar](50) NULL,

[start_date] [varchar](50) NULL,

[UNIQUEID] [varchar](16) NOT NULL,

PRIMARY KEY CLUSTERED ([UNIQUEID] ASC)

)

INSERT INTO @wce_ilr(uniqueid, COMPANY_Contact, company_name, Learner_Name, Learner_Surname, course_name, course_title,start_date)

SELECT '1','john Smith','Tesco','paul','king','making rods','rods','10/01/2009' UNION ALL

SELECT '2','james olla','Sainsburies','paul','king','making cars','cars','11/01/2009' UNION ALL

SELECT '3','fred hick','kwik fit','paul','king','making bikes','bikes','12/01/2009' UNION ALL

SELECT '4','mark peters','coop','paul','king','making trains','trains','13/01/2009' UNION ALL

SELECT '5','sam roberts','mistral','paul','king','making planes','planes','14/01/2009' UNION ALL

SELECT '6','peter jones','baitmans','paul','king','making shoes','shoes','15/01/2009'

-- Extract contact information

INSERT INTO @wce_contact (

COMPANY_Contact,

company_name,

Learner_Name,

Learner_Surname,

UNIQUEID

)

SELECT

ilr.COMPANY_Contact,

ilr.company_name,

ilr.Learner_Name,

ilr.Learner_Surname,

ilr.UNIQUEID

FROM @wce_ilr ilr

LEFT JOIN @wce_contact cnt ON ilr.UNIQUEID = cnt.UNIQUEID

WHERE cnt.UNIQUEID IS NULL

-- Extract course information

INSERT INTO @wce_delegate_course_link (

course_name,

course_title,

start_date,

UNIQUEID

)

SELECT

ilr.course_name,

ilr.course_title,

ilr.start_date,

ilr.UNIQUEID

FROM @wce_ilr ilr

LEFT JOIN @wce_delegate_course_link crs ON ilr.UNIQUEID = crs.UNIQUEID

WHERE crs.UNIQUEID IS NULL

SELECT * FROM @wce_ilr

SELECT * FROM @wce_contact

SELECT * FROM @wce_delegate_course_link

You might consider to use a link table between the contacts and the courses to avoid duplicate data.

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

sc-w Hall of Fame Points: 3664 More actions · Answer 9

Thanks for your help Flo, i am now trying to apply this with the actual structure here, fingers crossed but i already have a head full of questions so keep an eye open for my reply once i have attempted it 🙂 Thanks again.

sc-w Hall of Fame Points: 3664 More actions · Answer 10

Hi Again,

Ok, here it is, the sample code i gave you was a mini replication of what i need to do, the field names and stuff are diffrent but i though it would be easier for me to show and understand if i used a simple layout.

I have now gone through my live data and manipulated your example to get to work with the fields / tables i am using. After some trouble shooting i got SQL browser to execute the code with no errors! However, i need it to execute in a stored procedure. When i move the code into my stored procedure i am getting an error near my first declare statement.

Can you see anything obvious that is wrong? Once this is working i can test my code and add the enhancements to make it do exactly what i need based on other factors not yet explained.

Msg 156, Level 15, State 1, Procedure move_ebs_learner, Line 5

Incorrect syntax near the keyword 'DECLARE'.

USE [wce_snc]

GO

/****** Object: StoredProcedure [dbo].[move_ebs_learner] Script Date: 04/09/2009 15:20:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[move_ebs_learner]

(

-- wce_ilr is the source table to be broken down in to the following destination tables.

DECLARE @wce_ilr TABLE (

[Created_date] [smalldatetime] NULL,--All tables involved.

[Funding_Year] [varchar](2) NULL,--Booking Table-wce_delegate_link

[LSC_Funding_Stream] [varchar](2) NULL,--Booking Table-wce_delegate_link

[Unit_Instance_Code] [varchar](12) NULL,--Booking Table-wce_delegate_link

[Batch_Code] [varchar](12) NULL,--Booking Table-wce_delegate_link

[Full_Co_Funded] [varchar](50) NULL,--Booking Table-wce_delegate_link

[Long_Description] [varchar](150) NULL,--Booking Table-wce_delegate_link

[Notional_NVQ_Level_Code] [varchar](2) NULL,--Booking Table-wce_delegate_link

[Learning_Aim_Title] [varchar](150) NOT NULL,--Booking Table-wce_delegate_link

[Prog_Type] [varchar](10) NOT NULL,--Booking Table-wce_delegate_link

[Learning_Aim] [varchar](10) NOT NULL,--Booking Table-wce_delegate_link

[Awarding_Body_Code] [varchar](8) NOT NULL,--Booking Table-wce_delegate_link

[GLH] [decimal](5, 0) NOT NULL,--Booking Table-wce_delegate_link

[Venue] [varchar](50) NOT NULL,--Booking Table-wce_delegate_link

[Main_Delivery_Method] [varchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Prior_Attain_Level] [varchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Enhanced_Funded] [varchar](150) NOT NULL,--Booking Table-wce_delegate_link

[CurrentStatus] [varchar](150) NOT NULL,--Booking Table-wce_delegate_link

[Employ_Status_Begin] [varchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Prior_Learner_Status] [nvarchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Start_Date] [smalldatetime] NOT NULL,--Booking Table-wce_delegate_link

[Exp_End_Date] [smalldatetime] NOT NULL,--Booking Table-wce_delegate_link

[End_Date] [smalldatetime] NOT NULL,--Booking Table-wce_delegate_link

[Completion] [varchar](1) NOT NULL,--Booking Table-wce_delegate_link

[TTG_High_Low_Rate] [varchar](1) NOT NULL,--Booking Table-wce_delegate_link

[Outcome] [varchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Learning_Difficulty] [varchar](2) NOT NULL,--Booking Table-wce_delegate_link

[Learning_Diff] [varchar](10) NOT NULL,--Booking Table-wce_delegate_link

[Partner_Code] [varchar](30) NOT NULL,--Booking/Partner Table-wce_delegate_link/wce_contact

[Partner_Name] [varchar](150) NOT NULL,--Booking/Partner Table-wce_delegate_link/wce_contact

[Person_Code] [decimal](10, 0) NOT NULL,--Booking/Learner Table-wce_delegate_link/wce_contact

[Unique_Learner_No] [decimal](10, 0) NOT NULL,--Learner Table-wce_contact

[Student_Name] [varchar](100) NOT NULL,--Learner Table-wce_contact

[Surname] [varchar](50) NOT NULL,--Learner Table-wce_contact

[ForeName] [varchar](50) NOT NULL,--Learner Table-wce_contact

[Middle_Names] [varchar](50) NOT NULL,--Learner Table-wce_contact

[Sex] [varchar](50) NOT NULL,--Learner Table-wce_contact

[Date_of_Birth] [smalldatetime] NOT NULL,--Learner Table-wce_contact

[NI_Number] [varchar](150) NOT NULL,--Learner Table-wce_contact

[Disability] [varchar](2) NOT NULL,--Learner Table-wce_contact

[Ethnicity] [varchar](10) NOT NULL,--Learner Table-wce_contact

[Student_Address_1] [varchar](80) NOT NULL,--Learner Table-wce_contact

[Student_Address_2] [varchar](80) NOT NULL,--Learner Table-wce_contact

[Student_Address_3] [varchar](50) NOT NULL,--Learner Table-wce_contact

[Student_Address_4] [varchar](50) NOT NULL,--Learner Table-wce_contact

[Student_Pcode_1] [varchar](4) NOT NULL,--Learner Table-wce_contact

[Student_Pcode_2] [varchar](4) NOT NULL,--Learner Table-wce_contact

[Email_Address] [varchar](100) NOT NULL,--Learner Table-wce_contact

[Mobile_Phone_Number] [varchar](22) NOT NULL,--Learner Table-wce_contact

[Telephone] [varchar](25) NOT NULL,--Learner Table-wce_contact

[Telephone_Line_2] [varchar](25) NOT NULL,--Learner Table-wce_contact

[EDRS] [varchar](50) NOT NULL,--Company Table-wce_contact

[Emp_Address_1] [varchar](80) NOT NULL,--Company Table-wce_contact

[Emp_Address_2] [varchar](80) NOT NULL,--Company Table-wce_contact

[Emp_Address_3] [varchar](50) NOT NULL,--Company Table-wce_contact

[Emp_Address_4] [varchar](50) NOT NULL,--Company Table-wce_contact

[Emp_Address_Pcode1] [varchar](4) NOT NULL,--Company Table-wce_contact

[Emp_Address_Pcode2] [varchar](4) NOT NULL,--Company Table-wce_contact

PRIMARY KEY CLUSTERED ([Learning_Aim] ASC)

)

-- Destination Table for for Company, Learner and Partner info. Each type of data is determined

-- by record_type beign set Company, Learner or Partner when a reow is added, in this instance the

-- above destination table wce_ilr need to create a new row for each type from one row in the wce_ilr

-- table.

DECLARE @wce_contact TABLE (

[uniqueid] [varchar](16),

[CreateTime] [datetime] NULL,

[Record_Type] [varchar](30) NULL,

[Contact] [varchar](50) NULL,

[firstname] [varchar](25) NULL,

[surname] [varchar](25) NULL,

[middle_name] [varchar](45) NULL,

[Gender] [varchar](15) NULL,

[DOB] [datetime] NULL,

[National_Insurance_No] [varchar](25) NULL,

[Disability] [varchar](75) NULL,

[Ethnicity] [varchar](75) NULL,

[Partner_Code] [varchar](75) NULL,

[Unique_Learner_Ref] [varchar](45) NULL,

[SNC_Student_Number] [varchar](25) NULL,

[company] [varchar](50) NULL,

[address1] [varchar](50) NULL,

[address2] [varchar](30) NULL,

[city] [varchar](30) NULL,

[COUNTY] [varchar](25) NULL,

[Postcode] [varchar](25) NULL,

[Postcode2] [varchar](25) NULL,

[EMAILADDRESS] [varchar](75) NULL,

[MOBILEPHONE] [varchar](30) NULL,

[phone] [varchar](30) NULL,

[altphone] [varchar](30) NULL,

[EDRS] [varchar](75) NULL

PRIMARY KEY CLUSTERED ([UNIQUEID] ASC)

)

-- Contains fields from the wce_ilr table that are related to the course, learner and partner.

DECLARE @wce_course_delegate_link TABLE (

[uniqueid] [varchar](16),

[CreateTime] [datetime] NULL,

[Funding_Year] [varchar](2) NULL,

[Funding_Stream] [varchar](2) NULL,

[SNO_Code] [varchar](12) NULL,

[Batch_Code] [varchar](12) NULL,

[Funding_Type] [varchar](50) NULL,

[Course_Name] [varchar](75) NULL,

[Qualification_Level] [varchar](45) NULL,

[Qualification_Title] [varchar](75) NULL,

[Course_Source] [varchar](45) NULL,

[LAD_Learning_Aim_Reference] [varchar](50) NULL,

[Awarding_Body] [varchar](75) NULL,

[Guided_Learning_Hours] [decimal](5, 0) NULL,

[Venue] [varchar](50) NULL,

[Delivery_Method] [varchar](2) NULL,

[Prior_Attainment_Level] [varchar](2) NULL,

[Enhanced_Funding] [varchar](100) NULL,

[Current_Status] [varchar](50) NULL,

[Employment_Status] [varchar](2) NULL,

[Prior_Learning_Status] [nvarchar](2) NULL,

[start_date] [datetime] NULL,

[Exp_End_Date] [datetime] NULL,

[end_date] [datetime] NULL,

[Completion] [varchar](1) NULL,

[TTG_High_Low_Rate] [varchar](1) NULL,

[Outcome] [varchar](2) NULL,

[Learning_Difficulty] [varchar](2) NULL,

[Learn_Diff] [varchar](10) NULL,

[Partner_Code] [varchar](10) NULL,

[Partner_Name] [varchar](45) NULL,

[SNC_Student_Number] [decimal](10, 0) NULL

PRIMARY KEY CLUSTERED ([UNIQUEID] ASC)

)

--- Extract and insert learner information

INSERT INTO @wce_contact (

UNIQUEID,

createtime,

contact,

firstname,

surname,

middle_name

)

SELECT

ilr.learning_aim,

ilr.created_date,

ilr.student_name,

ilr.forename,

ilr.surname,

ilr.middle_names

FROM @wce_ilr ilr

LEFT JOIN @wce_contact cnt ON ilr.learning_aim = cnt.UNIQUEID

WHERE cnt.UNIQUEID IS NULL

--- Extract and insert course information

INSERT INTO @wce_course_delegate_link (

course_name,

qualification_title,

start_date,

UNIQUEID

)

SELECT

ilr.Long_Description,

ilr.Learning_aim_title,

ilr.start_date,

ilr.learning_aim

FROM @wce_ilr ilr

LEFT JOIN @wce_course_delegate_link cdl ON ilr.learning_aim = cdl.UNIQUEID

WHERE cdl.UNIQUEID IS NULL

SELECT * FROM @wce_ilr

SELECT * FROM @wce_contact

SELECT * FROM @wce_course_delegate_link

sc-w Hall of Fame Points: 3664 More actions · Answer 11