Moving data from one table to 3

  • 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)

  • 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.

  • 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

  • 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.

  • 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.

  • 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

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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • sward (4/9/2009)


    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

    May I ask what may be a stupid question, what are you trying to accomplish with this stored procedure? Is it to move the data from one existing table to three new tables?

  • Hi,

    Not a stupid question, it is to move data from one table into two tables actually but in one of the two table wce_contact, there needs to be two rows added. There are certain other factors that i will need to apply when this logic works, for example there might be multiple rows that have the same learner but i only wnat him added to the wce_contact table and the same for the company which is heald in the same row. However, the multiple rows contain different course information that have to be added as individual rows in the wce_course_delegate_link table.

    does that make sense?

  • Hi sward

    The error message

    Your code:

    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 (

    The error message is because after your procedure name you need either to declare variables or an "AS" (also after the variables).

    Lynn's question (more important)

    The procedure you posted looks very equal to my post. This was an example all these table declarations have been to simulate your data. You have to handle your real tables instead of my tables. 😉

    Greets

    Flo

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

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