13

I have a table with a single column, which is an auto-generated identity

create table SingleIdTable (
   id int identity(1,1) not null
)

I can insert a single row with an auto-generated id with:

insert into SingleIdTable default values

I want to insert many rows and use the output syntax to get their ids, something like:

insert into SingleIdTable
output inserted.Id into @TableOfIds
    select (default values) from SomeOtherTable where Attribute is null

Where the intention is to insert a row into SingleIdTable for each row in SomeOtherTable where Attribute is null using an auto-generated id. The above doesn't work, but how could I do it. I note that if my table had more than just a single column I could do it, but I can't select empty rows which is what I really want to do.

I can't change the definition of SomeOtherTable.

Silas Davis
  • 712
  • 1
  • 9
  • 15

2 Answers2

24

If SQL Server 2008+ you can use MERGE for this. Example syntax below.

MERGE INTO SingleIdTable
USING (SELECT *
       FROM   SomeOtherTable
       WHERE  Attribute IS NULL) T
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES
OUTPUT INSERTED.id;

I'm not sure what practical use this single column table has though?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Cunning... The table an artefact of a someone misapplying NHibernate when mapping a group. I will accept this, but I now realise I could do with correlating the inner query with the insert so that I can `output inserted.id, SomeOtherTable.id)`. Is this possible? Really I just need to match my bag of generated ids each with an element of `SomeOtherTable` where `Attribute` is null. Any thoughts? – Silas Davis Oct 12 '12 at 17:14
  • @silasdavis - Yes just use `OUTPUT INSERTED.id, T.id` – Martin Smith Oct 12 '12 at 17:15
  • oh of course, thanks I didn't think I was going to be able to do this! – Silas Davis Oct 12 '12 at 17:19
  • And again merge can be used as a "hack solution" for a problem. Also used this kind of a solution for matching old ID's with new ID's. Still think it's ugly but it works, better than using a cursor. Thx – Edwin Stoteler Mar 31 '14 at 07:25
  • A practical use of this is the Tally table: http://www.sqlservercentral.com/articles/T-SQL/62867/ – kevinpo Feb 08 '17 at 17:57
1

you did not specify which version of SQL Server you are on. If you happen to be on SQL 2012 you probably can replace you SingleIdTable with a sequence: http://msdn.microsoft.com/en-us/library/ff878091.aspx

Sebastian Meine
  • 11,260
  • 29
  • 41