April 9, 2009 at 1:53 pm
Hi Flo,
The tables i have provided are the exact replication of what i have. The demo code you setup has been replicated because the logic i think will work. Of course i have no doubt in my mind that i am going to need some expert advise through this process 🙂 The only thing i took out of your sample code was the 6 rows of test data but wce_ilr table have 8000 rows so figured that was not required.
I will test it and will reply by tomorrow i hope. Thanks for keeping an eye on the post.
April 9, 2009 at 2:17 pm
Hi sward
Here a little walk-through.
ALTER procedure [dbo].[move_ebs_learner]
(
Remove the brace in the second line.
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)
)
Remove all those lines. This was only my test table. A table with a trailing @ is a variable which I used for my example. You don't need it for your real procedure.
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
Remove all the @-signs from my table names to handle with your real tables. Add your real destination columns to the column list within the braces within the INSERT INTO parts and add the real source columns into the SELECT part.
SELECT * FROM @wce_ilr
SELECT * FROM @wce_contact
SELECT * FROM @wce_course_delegate_link
Remove those lines, it was just for my sample output.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 9, 2009 at 3:08 pm
Hey Flo,
I'm still not getting it right. i had the -- lines for personal comments to reference the tables those fields need to be inserted. I took them all out though. Removed the brace. and took the select statements out at the end.
I also removed all the @ but i thought they were needed for the variables? after the declare.
All the table names and fields are real, and used in the CRM system that i am working in the wce_ilr table is provided to me by a college, they created that structure and will populate that table every night, then i have to move the data to the other tables. In the insert statements i have only referenced a few fields to make sure it works, i will then add the rest from the wce_ilr table.
Hope i am being clear but there are more errors now.
Thanks
Sergio
Here is the error:
Msg 102, Level 15, State 1, Procedure move_ebs_learner, Line 3
Incorrect syntax near 'wce_ilr'.
Msg 1087, Level 15, State 2, Procedure move_ebs_learner, Line 148
Must declare the table variable "@wce_ilr".
Msg 102, Level 15, State 1, Procedure move_ebs_learner, Line 157
Incorrect syntax near ')'.
USE [wce_snc]
GO
/****** Object: StoredProcedure [dbo].[move_ebs_learner] Script Date: 04/09/2009 15:20:12 ******/
SET ANSI_NULLS ON
GO
ALTER procedure [dbo].[move_ebs_learner]
DECLARE wce_ilr TABLE (
[Created_date] [smalldatetime] NULL,
[Funding_Year] [varchar](2) NULL,
[LSC_Funding_Stream] [varchar](2) NULL,
[Unit_Instance_Code] [varchar](12) NULL,
[Batch_Code] [varchar](12) NULL,
[Full_Co_Funded] [varchar](50) NULL,
[Long_Description] [varchar](150) NULL,
[Notional_NVQ_Level_Code] [varchar](2) NULL,
[Learning_Aim_Title] [varchar](150) NOT NULL,
[Prog_Type] [varchar](10) NOT NULL,
[Learning_Aim] [varchar](10) NOT NULL,
[Awarding_Body_Code] [varchar](8) NOT NULL,
[GLH] [decimal](5, 0) NOT NULL,
[Venue] [varchar](50) NOT NULL,
[Main_Delivery_Method] [varchar](2) NOT NULL,
[Prior_Attain_Level] [varchar](2) NOT NULL,
[Enhanced_Funded] [varchar](150) NOT NULL,
[CurrentStatus] [varchar](150) NOT NULL,
[Employ_Status_Begin] [varchar](2) NOT NULL,
[Prior_Learner_Status] [nvarchar](2) NOT NULL,
[Start_Date] [smalldatetime] NOT NULL,
[Exp_End_Date] [smalldatetime] NOT NULL,
[End_Date] [smalldatetime] NOT NULL,
[Completion] [varchar](1) NOT NULL,
[TTG_High_Low_Rate] [varchar](1) NOT NULL,
[Outcome] [varchar](2) NOT NULL,
[Learning_Difficulty] [varchar](2) NOT NULL,
[Learning_Diff] [varchar](10) NOT NULL,
[Partner_Code] [varchar](30) NOT NULL,
[Partner_Name] [varchar](150) NOT NULL,
[Person_Code] [decimal](10, 0) NOT NULL,
[Unique_Learner_No] [decimal](10, 0) NOT NULL,
[Student_Name] [varchar](100) NOT NULL,
[Surname] [varchar](50) NOT NULL,
[ForeName] [varchar](50) NOT NULL,
[Middle_Names] [varchar](50) NOT NULL,
[Sex] [varchar](50) NOT NULL,
[Date_of_Birth] [smalldatetime] NOT NULL,
[NI_Number] [varchar](150) NOT NULL,
[Disability] [varchar](2) NOT NULL,
[Ethnicity] [varchar](10) NOT NULL,
[Student_Address_1] [varchar](80) NOT NULL,
[Student_Address_2] [varchar](80) NOT NULL,
[Student_Address_3] [varchar](50) NOT NULL,
[Student_Address_4] [varchar](50) NOT NULL,
[Student_Pcode_1] [varchar](4) NOT NULL,
[Student_Pcode_2] [varchar](4) NOT NULL,
[Email_Address] [varchar](100) NOT NULL,
[Mobile_Phone_Number] [varchar](22) NOT NULL,
[Telephone] [varchar](25) NOT NULL,
[Telephone_Line_2] [varchar](25) NOT NULL,
[EDRS] [varchar](50) NOT NULL,
[Emp_Address_1] [varchar](80) NOT NULL,
[Emp_Address_2] [varchar](80) NOT NULL,
[Emp_Address_3] [varchar](50) NOT NULL,
[Emp_Address_4] [varchar](50) NOT NULL,
[Emp_Address_Pcode1] [varchar](4) NOT NULL,
[Emp_Address_Pcode2] [varchar](4) NOT NULL,
PRIMARY KEY CLUSTERED ([Learning_Aim] ASC)
)
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)
)
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)
)
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
INSERT INTO wce_course_delegate_link (
course_name,
qualification_title,
start_date,
UNIQUEID
)
April 9, 2009 at 3:12 pm
Remove all those "DECLARE *** TABLE " parts. Just leave the INSERT statements and add your destination and source columns.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply