SQL Server : insert data set into table with an identity column

Question

I have a table with an auto-incrementing identity column. Typically I might insert data as follows

INSERT INTO [dbo].[table] 
DEFAULT VALUES;

SET @value = SCOPE_IDENTITY();

This way I know the identity value I've just inserted. However I need to insert a "set" of values into that table. Preferably also be able to identify the values I just inserted. I was hoping something similar to the following would be possible ...

INSERT INTO dbo.table DEFAULT VALUES
OUTPUT INSERTED.id INTO @output
SELECT SCOPE_IDENTITY()   -- obviously this isn't possible and doesn't actually make sense
FROM @records
WHERE somecolumn IS NULL

I know I might need to set identity_insert on ... I would prefer not to if I don't have to. I am also aware that maybe I could also use some sort of recursive CTE, though I haven't used one of those in a while. Any help would be appreciated.

EDIT: to be clear the question I am asking is: how do I insert a "SET" of data into a table with an auto-incrementing identity column. And hopefully identify the values I just inserted in some way.

Apologies if that was not clear. The question is how do I insert a set of values into a table with one column that is an identity column? — Kevin Donde, Jun 08 '17 at 01:37

score 1 · Answer 1 · answered Jun 08 '17 at 14:32

INSERT INTO [dbo].[table] 
DEFAULT VALUES;
SET @value = SCOPE_IDENTITY();

One does not "typically" do any such thing. It would be highly unusual (to be gentle) to insert a single row that consisted of nothing but default values. And inserting hundreds or thousands of rows is even more suspicious. I think you have chosen a path that doesn't completely make sense.

But let's assume you have not lost your senses. Unfortunately, you cannot insert multiple rows using the "default values" syntax (directly or indirectly). But we can kludge together a script that "sort of" does this (with assumptions) using the output clause suggested by both Gordon and Sachin (using tally table logic here).

set nocount on;

declare @id int; 
declare @outputtable table (id int);

create table #x (id int not null identity(1,1), descr varchar(20) null, dd int not null default(2)); 
insert #x (descr, dd) values ('test', 4), ('zork', 2), (null, 55); -- some extra fluff for demonstartion
insert #x default values; 
set @id = SCOPE_IDENTITY(); 
select @id; 
select * from #x order by id;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02 a, E02 b),
    E08(N) AS (SELECT 1 FROM E04 a, E04 b),
    E16(N) AS (SELECT 1 FROM E08 a, E08 b),
    E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
insert #x (descr, dd) 
output inserted.id into @outputtable(id) 
select src.descr, src.dd
from #x as src cross join cteTally 
where src.id = @id and cteTally.N < 5;

select x.*
from @outputtable as ids inner join #x as x on ids.id = x.id order by x.id; 

if object_id('tempdb..#x') is not null drop table #x;
go

This might not work depending on your table DDL. I'll let you find the assumptions built into this logic.

score 1 · Answer 2 · answered Sep 11 '21 at 21:59

For an identity column, there is only one way to do this that I am aware of. If you don't mind keeping the dummy around you can skip the alter table statements that add and remove it.

drop table if exists T;
create table T (
    id int identity(1, 1) not null
);

alter table T add dummy bit;

insert into T (dummy)
select null
from (
    values (42),(555),(911)
) v (v);

alter table T drop column dummy;

select * from T;

Gordon Linoff · Answer 3 · 2017-06-08T02:06:59.397

0

You are really close:

INSERT INTO dbo.table
     OUTPUT INSERTED.id INTO @output
     DEFAULT VALUES;

SELECT *  
FROM @output;

The INSERT puts the values into @output. You can then reference them. Remember to define @output as a table variable with a column of the correct type.

Here is a rextester example of it working.

EDIT:

I thought the problem was using @output, because your sample doesn't do that correctly. If your table has a single identity column, then I don't think that SQL Server provides a single-query mechanism for inserting multiple values, unless you turn off identity insert.

One option is a loop:

CREATE TABLE t (id int identity);

DECLARE @output table (id int);

DECLARE @i int = 1;

WHILE @i < 10
BEGIN
    INSERT INTO t
         OUTPUT INSERTED.id INTO @output
         DEFAULT VALUES;
    SET @i = @i + 1;
END;

SELECT *  
FROM @output;

Another option would be to include another column (even a dummy) just so you can insert something.

And finally, perhaps you don't need a table at all. Perhaps a sequence will suffice for your purposes.

edited Jun 08 '17 at 02:06

answered Jun 08 '17 at 01:30

Gordon Linoff

1,242,037
58
646
786

If i do that exactly I get an error "Incorrect syntax near 'output'" – Kevin Donde Jun 08 '17 at 01:41
@KevinDonde . . . I copied your syntax without looking closely. The `OUTPUT` clause comes before the `DEFAULT VALUES` clause. – Gordon Linoff Jun 08 '17 at 01:43
Hopefully it wasn't me that downvoted ... if so it was accidental. I just upvoted because that was helpful. However I still only get one value at a time. I need to be able to insert 10 or 100 at once. – Kevin Donde Jun 08 '17 at 01:53
@KevinDonde . . . You can't do that with `DEFAULT VALUES`. You would need to use a loop or have additional columns. Or, if you have no other columns, perhaps you really want a sequence instead. – Gordon Linoff Jun 08 '17 at 01:59
What do you mean by a sequence? – Kevin Donde Jun 08 '17 at 02:04
@KevinDonde . . . https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql. – Gordon Linoff Jun 08 '17 at 02:08

Maverick Sachin · Answer 4 · 2017-06-08T02:34:30.013

0

Try this query --

CREATE TABLE StudentPassMarks (ID INT identity(1, 1))

DECLARE @OutputTable TABLE (ID INT)

    INSERT INTO StudentPassMarks
        OUTPUT inserted.ID
    INTO @OutputTable(ID) 
    DEFAULT VALUES

SELECT * FROM @OutputTable

Go 20;

SELECT * FROM StudentPassMarks

edited Jun 08 '17 at 02:34

answered Jun 08 '17 at 01:58

Maverick Sachin

874
7
12

Thanks @Sachin but the StudentPassMarks table must have just the ID value in order for this to match my problem. – Kevin Donde Jun 08 '17 at 02:03
You can edit the number and type of columns you needed and make it work. If it helped you solve your problem don't forget to accept it as an answer. – Maverick Sachin Jun 08 '17 at 02:05
That doesn't solve my problem. It doesn't insert more than one value. – Kevin Donde Jun 08 '17 at 02:06
I've edited my answer. Is this what you are looking for ? – Maverick Sachin Jun 08 '17 at 02:18
I can't use a loop for performance reasons. I may have to process 100000 records. – Kevin Donde Jun 08 '17 at 02:18
One more edit and now I'm using Go 20 statement to make 20 inserts. Check it out. Does this work ? – Maverick Sachin Jun 08 '17 at 02:35
I sincerely appreciate your help ... all good options just not the one I need. I will still need to ensure an atomic transaction and I don't think a transaction will live across batches. I think I need to head in the direction of a recursive CTE – Kevin Donde Jun 08 '17 at 03:06

SQL Server : insert data set into table with an identity column

4 Answers4