Output multiple identity columns from temp table

Question

I'm using SQL Server 2K8 and have a table used solely to generate ids so that the primary key is unique across multiple tables -- the uniqueness across multiple tables is for an element hiearchy tree that requires each nodes to have a unique id regardless of type.

It only has one auto increment identity column from which I'd normally used @@IDENTITY to extract the ID if I were to insert a record one by one. However, I'm trying to optimize and do the inserts in a batch, but first need to generate a batch of IDs from this table.

CREATE TABLE [dbo].[MyTreeElementIDGenTab](
        [MyTreeElementID] [int] IDENTITY(1,1) NOT NULL
)

Also I know I could use a while loop/cursor, but was wondering if given a temp table of row data if I could use a sql batch statement to get the IDs out of this table to set in the temp table before inserting into the actual table.

I was thinking I could use ROW_NUMBER() to generate an Index and put the corresponding Index in the initial table, but didn't make any progress. Any help is appreciated!

DECLARE @NodeTypeATab TABLE
(
    NodeTypeATabId INT NULL,        -- To be populated by dbo.MyTreeElementIDGenTab
    Name NVARCHAR(MAX),
    ItemIndex INT NOT NULL      -- I can initially populate this from the client starting 
                            -- with Index # 1 for joining but maybe I don't need it?
)

-- Populate @NodeTypeATab with test data here

DECLARE @EntityIdTab TABLE
(   
    ElementId INT NOT NULL,
    ItemIndex INT NOT NULL
)

-- This below doesn't compile to generate a new ElementId to later be set in NodeTypeATabId above
-- I want to output the generated ID into the temp table but also have a "correlation/index ID" to set
-- back in the original table or some way if not via a correlation/index ID 

INSERT INTO dbo.MyTreeElementIDGenTab DEFAULT VALUES
OUTPUT INSERTED.MyTreeElementID, ROW_NUMBER() OVER(ORDER BY MyTreeElementID ASC) INTO @EntityIdTab
FROM @NodeTypeATab

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

Not sure that I understand where you want to put your generated id's but you can use merge and a numbers table to generate multiple id's at once. Here is some sample code that uses master..spt_values to generate 10 id's.

merge into MyTreeElementIDGenTab as T
using (select Number
       from master..spt_values
       where Number between 1 and 10 and
             [Type] = 'P') as S(I) 
on 0=1
when not matched then
  insert default values
output inserted.MyTreeElementID;

edited May 23 '17 at 12:19

Community

1
1

answered Oct 20 '11 at 21:18

Mikael Eriksson

136,425
22
210
281

1

Awesome sauce! MERGE to save the day again! I didn't think to do the INSERT DEFAULT VALUES when not matched trick and use 1 = 0 to get all the rows. I also outputted the generated id into the EntityIdTab and made ItemIndex an identity column so I could later go back to use that as a join "correlation id" with the original NodeTypeATab table on it's ItemIndex to update it's NodeTypeATabId with the generated id. – soundslike Oct 20 '11 at 21:44

score 1 · Answer 2 · answered Oct 20 '11 at 21:22

It sounds like you are trying to emulate the 'Sequence' construct that is going to be available in SQL Server Denali, there are a number of Sequence solutions for SQL Server posted around - but you need to know the term to search for to get the results.

For example, the following is a SQL CAT article relating to migration of Oracle Sequences (which provide what you are trying to do) into SQL Server.

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

An alternative to consider is to use GUID's to get a unique ID - that has been a pretty standard route to gettting unique ID's for rows, across an entire system.

Thanks -- I hadn't heard of Sequence construct before. – soundslike Oct 20 '11 at 21:44 — soundslike, Oct 20 '11 at 21:44

Output multiple identity columns from temp table

2 Answers2