Insert multiple rows of default values into a table

Question

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.

Martin Smith · Accepted Answer · 2012-10-12 16:52:32Z

25

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?

answered Oct 12, 2012 at 16:52

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Silas Davis Over a year ago

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?

Martin Smith Over a year ago

@silasdavis - Yes just use OUTPUT INSERTED.id, T.id

Silas Davis Over a year ago

oh of course, thanks I didn't think I was going to be able to do this!

Edwin Stoteler Over a year ago

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

kevinpo Over a year ago

A practical use of this is the Tally table: sqlservercentral.com/articles/T-SQL/62867

Sebastian Meine · Accepted Answer · 2012-10-12 17:13:48Z

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

answered Oct 12, 2012 at 17:13

Sebastian Meine

11.9k1 gold badge34 silver badges44 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows of default values into a table

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related