SQL Stored Proc Storing Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Stored Proc Storing Values

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-28 : 16:58:16

I have a SQL script that inserts records into a demographics table. It has a PK of ss_patient_id and is also an identity column. In my insert statement, I leave out the ss_patient_id so when the records get inserted, these records are automatically assigned with a new ss_patient_id. I'm able to insert these new records in the demographics using the ff stmt -

osql -E -h-1 -n -i C:\bcp\SelectDemographics.sql -S servernme

in a DOS batch file. My SQL script SelectDemographics.sql is -

USE DBTest
GO

Select a.*
From Demographics_Test a
Left Outer Join Demographics b
On a.Patient_ID = b.Patient_ID
And a.Last_Name = b.Last_Name
Where b.Patient_ID is null
And a.Last_Name = 'AINSLEY'
Order by Last_Name;
GO

If @@rowcount > 0
Insert Into Demographics (
[SS_Parent_ID],
[Last_Name],
[First_Name],
[Middle_Name],
[Patient_ID],
[SSTouch],
[Date_of_Registration],
[Address],
[Address2],
[City],
[State_Province],
[County],
[Country],
[Phone])
Select
a.SS_Parent_ID,
a.Last_Name,
a.First_Name,
a.Middle_Name,
a.Patient_ID,
a.SSTouch,
a.Date_of_Registration,
a.Address,
a.Address2,
a.City,
a.State_Province,
a.County,
a.Country,
a.Phone
From Demographics_Laptop a
Left Outer Join Demographics b
On a.Patient_ID = b.Patient_ID
And a.Last_Name = b.Last_Name
Where B.Patient_ID is null
And a.Last_Name = 'AINSLEY'
GO

Now, I need to update another table that is keyed by the same pk ss_patient_id. So, after inserting the reocrds in demographics, I need to somehow store the ss_patient_id values of the inserted records so I can use those to update the other table. How can I do that in my SQL scripts? Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 17:28:40

Assuming you are using sql 2005 or greater:
One way could be to use the OUTPUT INTO clause in your insert statemement to save the PK values into a table variable or temp table. Then use that to JOIN to your other table for your update.

ie:


create table #j (i int, PK int identity(1,1))

declare @output table (pk int)
insert #j (i) 
       output inserted.pk
       into   @output
select so.id 
from   sysobjects so

select j.*
from @output o
join   #j j on j.pk = o.pk

drop table #j

Be One with the Optimizer
TG

EDIT:
changed the code to capture an identity column

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-29 : 01:59:28

The scope_identity() function will return the last inserted identity value.

In your case after inserting on demographics get the values by using scope_identity() and insert in another table.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-29 : 02:44:18

Hi

How to get a batch of identity values without OUTPUT...
Refer:- http://weblogs.sqlteam.com/peterl/archive/2009/07/16/How-to-get-a-batch-of-identity-values-without-OUTPUT.aspx

-------------------------
R..

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 11:22:13

Thank you very much for the quick replies. I tried to use the OUTPUT clause in my script and after running the stored proc, my rows are NOT inserted. I'm expecting about 3 records to be inserted based on my where clause. Any help wuld again be greatly appreciated. My stored proc is-

Select a.*
From Demographics_Laptop a
Left Outer Join Demographics b
On a.Patient_ID = b.Patient_ID
And a.Last_Name = b.Last_Name
Where b.Patient_ID is null
And a.Last_Name = 'AINSLEY'
Order by Last_Name;
GO

DECLARE @Demographics_Output table(SSPatientID int,PatientID varchar(20),LastName varchar(40))

If @@rowcount > 0
Insert Into Apollo_Test.Demographics
([SS_Parent_ID],
[Last_Name],
[First_Name],
[Middle_Name],
[Patient_ID])
Output Inserted.SSPatientID, Inserted.PatientID, Inserted.LastName
Into @Demographics_Output (SSPatientID, PatientID, LastName)
Select
a.SS_Parent_ID,
a.Last_Name,
a.First_Name,
a.Middle_Name,
a.Patient_ID
From Demographics_Laptop a
Left Outer Join Demographics b
On a.Patient_ID = b.Patient_ID
And a.Last_Name = b.Last_Name
Where B.Patient_ID is null
And a.Last_Name = 'AINSLEY'

select * from @Demographics_Output;

select * from Demographics
where last_name = 'AINSLEY';

GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-29 : 11:45:17

Your rows are not inserted where? Demographics table or do you mean @demographics_output table var?

If you mean your Demographics table then one of your conditions is preventing it.

So either @@rowcount is not > 0 (from your first statement) or your WHERE clause is not allowing any rows returned in your second statement. Oh, I see those are the same statement

Have you run this code outside the context of an SP to test the conditions independently?

Be One with the Optimizer
TG

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 11:57:56

My initial post in this thread contains my insert SQL script that works just fine without any problems. I am able to insert new records into demographics table and like my initial question, I am just looking for a way to store the identity values that I am inserting so I can turn around and insert new related records in another table using the stored identity values. I just trimmed down the number of fields I have defined but it is the script except that I am trying to use the output clause and obviously something is wrong with how I am using the output clause. The last 2 SQL stmts are just for checking the results of my inserts.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-29 : 14:19:08

I think the problem may be that your [inserted] column names don't match the [Demographics] column names.

ie:

Demographics.Patient_ID
inserted.PatientID

and

Demographics.ss_patient_id
Inserted.SSPatientID

Be One with the Optimizer
TG

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 15:55:11

Man, you are exactly right. I didnt realize that the field names of the temporary table needed to be the same as the production table. Thanks a bunch.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-29 : 16:05:21

Cool. You're welcome. But technically, the temp table can have any column names you want. The [inserted] column references, however, are one-to-one with the columns of the table to which you are inserting. Just like they would be in an AFTER INSERT trigger.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources