April 7, 2009 at 6:49 am
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)
April 7, 2009 at 12:57 pm
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.
April 7, 2009 at 1:24 pm
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
April 8, 2009 at 9:30 am
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.
April 8, 2009 at 9:47 am
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.
April 8, 2009 at 10:01 am
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
April 8, 2009 at 10:34 am
Sample data, expected results based on the sample data? Would be of great value.
April 8, 2009 at 11:54 am
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
April 8, 2009 at 1:17 pm
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
April 9, 2009 at 5:15 am
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.
April 9, 2009 at 8:44 am
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
April 9, 2009 at 8:45 am
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
April 9, 2009 at 9:03 am
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?
April 9, 2009 at 10:01 am
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?
April 9, 2009 at 11:36 am
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