Capturing multiple SCOPE_IDENTITYs with OUTPUT

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

SQL Server 2005 Forums

Transact-SQL (2005)

Capturing multiple SCOPE_IDENTITYs with OUTPUT

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-10 : 05:04:22

I have a table variable containing UserID values. I can insert these into a database table using


INSERT INTO myTable
  userID
SELECT
  userID
FROM
  @tbl_userIDs

but I'd like to be able to grab all the ID values created in myTable to then use in a further INSERT statement. Is it possible to capture them all in one go using OUTPUT, or is there a better way?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-10 : 05:07:07

Put them in a table like in my example on the other thread.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145759

(2nd post)..

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-10 : 06:16:43

Hi RickD

Yes this was the basis for my post. I can't work out the syntax for doing this, as it seems I need to insert into the table from my temp table, then at the same time insert the ID values into the next table, all in one go! Is that correct? Can you share a rough code example with me please so I can work from that?!?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-10 : 07:30:18

No, create a temp to hold the ID's and any other fields you want to identify the inserted row from, then this can be used to insert anytime in the stored proc, not just immediately after..


Create table #temp ( id int identity(1,1), [desc] varchar(100), [desc2] varchar(100)) 
Create table #tmpids ( id int, [desc] varchar(100))

INSERT INTO #temp
OUTPUT
	Inserted.id
	,Inserted.desc
INTO
	#tmpids
SELECT 'desc7', 'desc1'
UNION SELECT 'desc6', 'desc2'
UNION SELECT 'desc5', 'desc3'
UNION SELECT 'desc4', 'desc4'
UNION SELECT 'desc3', 'desc5'
UNION SELECT 'desc2', 'desc6'
UNION SELECT 'desc1', 'desc7'

select 'whatever you want to do here'

SELECT * FROM #tmpids

This way, you have all your ids and can match them back up to a record, even after doing other processing.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-10 : 08:24:31

Thanks for your example. I see now that have have two INTO words in your statement, which is the part I couldn't figure out!

I presume your example works just as well with table variables?

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-10 : 08:37:45

Sorry Rick but I can't get it to work.

Please see this example code:


DECLARE @userIDs TABLE (userID int not null)
INSERT INTO @userIDs (userID) VALUES (34)
INSERT INTO @userIDs (userID) VALUES (85)
INSERT INTO @userIDs (userID) VALUES (89)
INSERT INTO @userIDs (userID) VALUES (97)

DECLARE @tempIds TABLE (ID int not null)
DECLARE @var1 nvarchar(50)
DECLARE @var2 nvarchar(50)
DECLARE @var3 int

INSERT INTO tbl_someTable -- <-- need to capture all ID's created at this point into @tempIds
  (
   userID,
   var1col,
   var2col,
   var3col
  )
SELECT
  userID,
  @var1,
  @var2,
  @var3
FROM
  @userIDs

As I'm only needing to capture one single column (identity column value from tbl_someTable) in the OUTPUT, how do I write this?? In your code you seem to specify all columns to begin with, then just nvarchars.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-10 : 11:39:45

Given your example:

DECLARE @userIDs TABLE (userID int not null)
INSERT INTO @userIDs (userID) VALUES (34)
INSERT INTO @userIDs (userID) VALUES (85)
INSERT INTO @userIDs (userID) VALUES (89)
INSERT INTO @userIDs (userID) VALUES (97)

DECLARE @tempIds TABLE (ID int not null)
DECLARE @var1 nvarchar(50)
DECLARE @var2 nvarchar(50)
DECLARE @var3 int

INSERT INTO tbl_someTable -- <-- need to capture all ID's created at this point into @tempIds
  (
   userID,
   var1col,
   var2col,
   var3col
  )
OUTPUT
	Inserted.MyTableID -- Whatever the ID is from table tbl_someTable
INTO
	@tempIds
SELECT
  userID,
  @var1,
  @var2,
  @var3
FROM
  @userIDs

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-10 : 13:29:34

That works wonderfully - thank you Lamprey.

Just one thing, is there any rules for how you specify the OUTPUT columns? I note that in previous posts in this thread, the OUTPUT columns are specified, but not the columns those values will be placed into, e.g.


INSERT INTO #temp
OUTPUT
	Inserted.id      /* <--- columns explicitly stated here */
	,Inserted.desc   /* <--- columns explicitly stated here */
INTO
	#tmpids          /* <--- columns not stated here */
SELECT 'desc7', 'desc1'
UNION SELECT 'desc6', 'desc2'
UNION SELECT 'desc5', 'desc3'
UNION SELECT 'desc4', 'desc4'

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-11 : 04:05:43

If you have the fields in order, you do not need to say where they go in the table. You can do:

INTO #tmpids (id, desc)

if you want to make it clearer.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-11 : 04:46:23

quote:
Originally posted by RickD

You can do:
INTO #tmpids (id, desc)

Fantastic. Thank you very much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources