Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
10 days, 14 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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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 3 days ago
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
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month10 days, 14 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.