Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Course Of The Month
3 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
need help constructing a stored procedure
Posted on 2011-03-13
I have 2 tables
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha NDate XDate YDate
0 1 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
Second Table
Person ID prmDate Completed Date_paid report_date
82333 6/1/2000 4/5/2001 8/1/2001
32322 4/1/2009 Yes 8/4/2005
92888 7/1/2005 Yes 10/1/2009
Question; Id like to create a Insert stored procedure that would output to the first table based on the data
on second table.
For example for personID 32322 in second table if field prmDate is populated('4/1/2009') it would insert 28 in the keycode and '4/1/2009' in the Ncode. This action can be done only if prmDate does not exist in first table. If keycode 28 already exists in first table, ignore it and do not insert Same logic also apply Date_paid(XDate in first table) and report Date. It would insert 49,60 depending what dates are populated in second table.
Generally First table can have mulitple records for same ID. I m assuming that the stored procedure will
have many INsert statements for each Date to test if exists...
I have about many records to loop and apply the stored procedure.
Please ask question if not clear.
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha NDate XDate YDate
0 1 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
Second Table
Person ID prmDate Completed Date_paid report_date
82333 6/1/2000 4/5/2001 8/1/2001
32322 4/1/2009 Yes 8/4/2005
92888 7/1/2005 Yes 10/1/2009
Question; Id like to create a Insert stored procedure that would output to the first table based on the data
on second table.
For example for personID 32322 in second table if field prmDate is populated('4/1/2009') it would insert 28 in the keycode and '4/1/2009' in the Ncode. This action can be done only if prmDate does not exist in first table. If keycode 28 already exists in first table, ignore it and do not insert Same logic also apply Date_paid(XDate in first table) and report Date. It would insert 49,60 depending what dates are populated in second table.
Generally First table can have mulitple records for same ID. I m assuming that the stored procedure will
have many INsert statements for each Date to test if exists...
I have about many records to loop and apply the stored procedure.
Please ask question if not clear.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
20
-
12
32 Comments
you said it to be insert stored procedure.
does this stored procedure will perform insert into second table, and then insert into first table accordingly ?
or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) ?
does this stored procedure will perform insert into second table, and then insert into first table accordingly ?
or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) ?
Active today
Inset only to First table, NO updates..
>>>or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) YES
I think there should be a link between 2 tables which is personID then, check first if for example
code 28 exists --this is the prmdate field in the second table which is the Ndate value in first table,
if exist ignore it , if not insert code 28 and the Ndate in first table..
This would be the same method for inserting record for codes 60,37,49 in first table
For every code inserted, the dates or 'Yes; values are also inserted with it.
>>>or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) YES
I think there should be a link between 2 tables which is personID then, check first if for example
code 28 exists --this is the prmdate field in the second table which is the Ndate value in first table,
if exist ignore it , if not insert code 28 and the Ndate in first table..
This would be the same method for inserting record for codes 60,37,49 in first table
For every code inserted, the dates or 'Yes; values are also inserted with it.
so, the mapping is as following ?
KeyCode 2nd table Field 1st Table Field
60 Completed Alpha
28 prmDate NDate
49 Date_Paid XDate
37 report_date YDate
KeyCode 2nd table Field 1st Table Field
60 Completed Alpha
28 prmDate NDate
49 Date_Paid XDate
37 report_date YDate
Active today
CORRECT! those are only 3 examples of the many codes that have to be inserted in the first table along with dates and alpha values, So one personID can have many records in the first table
Since I don't know what is your data structure.
I just assume by your given example that, if there is no data, it will be blank string.
and since it can be blank string, data type should be char, not datetime or int etc.
here is the code
I just assume by your given example that, if there is no data, it will be blank string.
and since it can be blank string, data type should be char, not datetime or int etc.
here is the code
CREATE PROCEDURE CheckFirstTable
@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate varchar(20)
Declare @XDate varchar(20)
Declare @YDate varchar(20)
-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID
-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, KeyValue varchar(20))
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha)
IF @NDate <> '' INSERT INTO @Temp VALUES (28, @Ndate)
IF @XDate <> '' INSERT INTO @Temp VALUES (49, @Xdate)
IF @YDate <> '' INSERT INTO @Temp VALUES (37, @Ydate)
-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable
SELECT
0, 1, @PersonID, T.KeyCode,
CASE T.KeyCode WHEN 60 THEN T.KeyValue ELSE '' END,
CASE T.KeyCode WHEN 28 THEN T.KeyValue ELSE '' END,
CASE T.KeyCode WHEN 49 THEN T.KeyValue ELSE '' END,
CASE T.KeyCode WHEN 37 THEN T.KeyValue ELSE '' END
FROM
@Temp T
WHERE
KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)
GO
@temp is temporary table "variable"
you may change it to temporary table
Declare @Temp TABLE (KeyCode int, KeyValue varchar(20)) --> CREATE TABLE #Temp (KeyCode int, KeyValue varchar(20))
and replace all @Temp with #Temp
but if you want to change it to Actual Table, then you might need to delete old data in the table before insert value into it.
you may change it to temporary table
Declare @Temp TABLE (KeyCode int, KeyValue varchar(20)) --> CREATE TABLE #Temp (KeyCode int, KeyValue varchar(20))
and replace all @Temp with #Temp
but if you want to change it to Actual Table, then you might need to delete old data in the table before insert value into it.
Active today
What do you mean by delete data from table?
What is a better method ? Use a temp variable or #Temp
Table ? Maybe I'm misunderstanding the concept ?
Of temp variable or temp table
What is a better method ? Use a temp variable or #Temp
Table ? Maybe I'm misunderstanding the concept ?
Of temp variable or temp table
uses either kind of temp table is fine.
(just some older version of MSSQL may not support table variable)
the delete I mention, is in case you create permanent table (so, you don't have to create it inside stored procedure)
if you are using temp table as give example, we don't have to worry about delete, since table always new create.
(just some older version of MSSQL may not support table variable)
the delete I mention, is in case you create permanent table (so, you don't have to create it inside stored procedure)
if you are using temp table as give example, we don't have to worry about delete, since table always new create.
Active today
the code as well as the values corresponding to it like
keycode 28 is 'prmdate' '4/1/2009' should be inserted as well
keycode 28 is 'prmdate' '4/1/2009' should be inserted as well
Active today
modified table structure for First table
Instead of this...
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha NDate XDate YDate
0 1 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
.. there willl be one field to handle all dates
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha Nfield XDate
0 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
Instead of this...
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha NDate XDate YDate
0 1 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
.. there willl be one field to handle all dates
First Table has ff. structure and sample data
coID smPID PersonID KeyCode Alpha Nfield XDate
0 32322 28 4/1/2009
0 1 32322 49 8/4/2005
0 1 32322 60 Yes
0 1 92888 28 7/1/2005
0 1 92888 60 Yes
0 1 92888 37 10/1/2009
Since "Date" field is datetime data type,
then back to sample table in your question.
the Second Table, for PersonID = 32322, the report_date have no value display.
in database, what is this field stored? is it NULL value?
then back to sample table in your question.
the Second Table, for PersonID = 32322, the report_date have no value display.
in database, what is this field stored? is it NULL value?
Active today
First of All,when Ipq copied and pasted,the fields were misaligned.All the date fields in the second table -prmdate,ndate,report date will be mapped to the xdate field of the second table,but with different keycodes.The alpha field will remain same.
Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes' or empty string ''
2. prmDate/Date_Paid/report_d
1. Completed, will stored either 'Yes' or empty string ''
2. prmDate/Date_Paid/report_d
PersonID prmDate Completed Date_paid report_date
82333 6/1/2000 4/5/2001 8/1/2001
32322 4/1/2009 Yes 8/4/2005 NULL
92888 7/1/2005 Yes NULL 10/1/2009
Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes' or empty string ''
2. prmDate/Date_Paid/report_d
and here is new stored procedure.
1. Completed, will stored either 'Yes' or empty string ''
2. prmDate/Date_Paid/report_d
PersonID prmDate Completed Date_paid report_date
82333 6/1/2000 4/5/2001 8/1/2001
32322 4/1/2009 Yes 8/4/2005 NULL
92888 7/1/2005 Yes NULL 10/1/2009
and here is new stored procedure.
CREATE PROCEDURE CheckFirstTable
@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate datetime
Declare @XDate datetime
Declare @YDate datetime
-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID
-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha varchar(20), XDate datetime)
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha, NULL)
IF @NDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @Ndate)
IF @XDate IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Xdate)
IF @YDate IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @Ydate)
-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
0, 1, @PersonID, T.KeyCode,
CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE '' END,
CASE T.KeyCode
WHEN 28 THEN T.XDate ELSE NULL END,
WHEN 49 THEN T.XDate ELSE NULL END,
WHEN 37 THEN T.XDate ELSE NULL END
FROM
@Temp T
WHERE
KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)
GO
replace part of code with below
.
.
.
SELECT
0, 1, @PersonID, T.KeyCode,
CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE '' END,
CASE WHEN T.KeyCode IN (28, 49, 37) THEN T.XDate ELSE NULL END
FROM
@Temp T
.
.
.
Active today
Got an insert fail error
Cannot insert the value NULL int column 'XDate'
first table;column does not allow nulls. statment terminated
Cannot insert the value NULL int column 'XDate'
first table;column does not allow nulls. statment terminated
Active today
I tried to change a date of one in the second table and run it and I get the error
"Syntax error converting the varchar value 'yes ' to a column of data type int."
notice how 'yes got extra spaces
"Syntax error converting the varchar value 'yes ' to a column of data type int."
notice how 'yes got extra spaces
As mention earlier that, I don't know your table structure
and how the data stored in case you consider there is no value
anyway, for error convert 'yes ' to int
mean Completed is char, while Alpha is int
then I will assume (again) that Alpha = 1 if yes
and how the data stored in case you consider there is no value
anyway, for error convert 'yes ' to int
mean Completed is char, while Alpha is int
then I will assume (again) that Alpha = 1 if yes
CREATE PROCEDURE CheckFirstTable
@PersonID int
AS
Declare @Completed varchar(20)
Declare @prmDate datetime
Declare @Date_Paid datetime
Declare @report_date datetime
-- get value for each variable
SELECT @Completed = Completed, @prmDate = prmDate, @Date_Paid = Date_Paid, @report_date = report_date
FROM SecondTable
WHERE PersonID = @PersonID
-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha int, XDate datetime)
IF RTRIM(@Completed) = 'yes' INSERT INTO @Temp VALUES (60, 1, NULL)
IF @prmDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @prmdate)
IF @Date_Paid IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Date_Paid)
IF @report_date IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @report_date)
-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
0, 1, @PersonID, T.KeyCode,
CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE 0 END,
CASE WHEN T.KeyCode IN (28, 49, 37) THEN T.XDate ELSE NULL END
FROM
@Temp T
WHERE
KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)
GO
Active today
alpha and completed have botg data types of CHAR.
I have 4 keys in the first table which are
coID smPID PersonID KeyCode
Using the previous sp (not the one above) I was able to insert the records. However for testing purposes, I changed the date paid under that personID, and that's when I get the error.
What I'd like the stored proc to do is to not to have the ability to change the xdate or yes values if they already exist in the first table even though it was changed in the second table. So there should be somekind of checking. I don;t know if the 4 primary keys would already be enough to handle that.
OK, I will test your new stored procedure.
I have 4 keys in the first table which are
coID smPID PersonID KeyCode
Using the previous sp (not the one above) I was able to insert the records. However for testing purposes, I changed the date paid under that personID, and that's when I get the error.
What I'd like the stored proc to do is to not to have the ability to change the xdate or yes values if they already exist in the first table even though it was changed in the second table. So there should be somekind of checking. I don;t know if the 4 primary keys would already be enough to handle that.
OK, I will test your new stored procedure.
my given code design to perform insert into first table for any missing related information base on second table.
if already exists, will just ignore, no matter value is matched with second table or not.
the code perform following step
1. read data from second table for input @PersonID and put in into variable
- you should adjust the data type to match with your table
2. check from variable to consider if it contain data, if it is, insert into @Temp table
- you have to adjust the check condition
for ex if RTRIM(@Completed) = 'yes'
if @prmDate IS NOT NULL -- if no data = NULL
if @prmDate = '01 JAN 1900' -- if no data contain date value as 01 JAN 1900
3. insert into first table for KeyCode which not there in first table for selected @PersonID
- I don't know the condition of coID or smPID, I just put fixed value base on your given example
- the CASE command, is to design which value to put into which field
so, field Alpha will put value if KeyCode = 60
and field XDate will put value if KeyCode in 28, 37, 48
- you may need to adjust the value after ELSE to record proper value for your "no data"
I wish this give you clear understanding on what is my process try to do, So you can adjust it to meet your requirement.
Let me know if anything unclear and further help need,
if already exists, will just ignore, no matter value is matched with second table or not.
the code perform following step
1. read data from second table for input @PersonID and put in into variable
- you should adjust the data type to match with your table
2. check from variable to consider if it contain data, if it is, insert into @Temp table
- you have to adjust the check condition
for ex if RTRIM(@Completed) = 'yes'
if @prmDate IS NOT NULL -- if no data = NULL
if @prmDate = '01 JAN 1900' -- if no data contain date value as 01 JAN 1900
3. insert into first table for KeyCode which not there in first table for selected @PersonID
- I don't know the condition of coID or smPID, I just put fixed value base on your given example
- the CASE command, is to design which value to put into which field
so, field Alpha will put value if KeyCode = 60
and field XDate will put value if KeyCode in 28, 37, 48
- you may need to adjust the value after ELSE to record proper value for your "no data"
I wish this give you clear understanding on what is my process try to do, So you can adjust it to meet your requirement.
Let me know if anything unclear and further help need,
Active today
Here's my tbl structure on First Table where the insert happens.hope it helps
coID smallint for this I enter 0
smPID tinyint always 1
PersonID int
KeyCode char
Alpha char
Nfield int always 0
XDate datetime
cc char always ''
cn tinyint always 0
bc char always ''
bn tinyint always 0
ba int always 0
hw char always 'K'
BTW, the alpha field should be char because it takes differenr description for different keycodes
coID smallint for this I enter 0
smPID tinyint always 1
PersonID int
KeyCode char
Alpha char
Nfield int always 0
XDate datetime
cc char always ''
cn tinyint always 0
bc char always ''
bn tinyint always 0
ba int always 0
hw char always 'K'
BTW, the alpha field should be char because it takes differenr description for different keycodes
Active today
Just to let you know,I stick with keeping Alpha and Completed fields to be chars data types. I modified and added the line If Rtrim('yes').....Thanks a lot. I never done anything like this before. Fantastic!
Featured Post
What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.)
But how far can you go?  When does the XML Source component become …
Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month3 days, 16 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.