How to insert 100 rows to a single table with only identity column?

Question

How to insert 100 rows to the following table using a single query?

CREATE TABLE #TEST1 (TEST_ID INT IDENTITY(1,1))

I tried below code

DECLARE @numRows int,@i int
SET @numRows = 100
SET @i=1

WHILE @i<@numRows
begin
    INSERT #TEST1 SELECT TOP 1 1 FROM sysobjects a
    SET @i=@i+1
end

but it is not working.

what have _you_ tried, where are _your_ code? – styx Mar 25 '18 at 08:18 — styx, Mar 25 '18 at 08:18
your table does not have any other column ? – Squirrel Mar 25 '18 at 08:30 — Squirrel, Mar 25 '18 at 08:30
Why you have tagged c#. No c# code in your question – SSD Mar 25 '18 at 08:37 — SSD, Mar 25 '18 at 08:37

score 5 · Accepted Answer · answered Mar 25 '18 at 08:44

You can try the following query

CREATE TABLE #TEST1 (TEST_ID INT IDENTITY(1,1))

SET IDENTITY_INSERT #TEST1 ON;

DECLARE @numRows int,@i int
SET @numRows = 100
SET @i=1

WHILE @i<@numRows
BEGIN
    INSERT #TEST1(TEST_ID) SELECT @i
    SET @i=@i+1
END
SET IDENTITY_INSERT #TEST1 OFF;

SELECT * FROM #TEST1
DROP TABLE #TEST1

Thanks

Gordon Linoff · Answer 2 · 2018-03-25T08:38:17.947

Offhand, I cannot think of a way of doing this with one statement. You could use the table master.dbo.spt_values:

set identity_insert #test1 off;

insert into #test1 (test_id)
    select top (100) row_number() over (order by (select null))
    from master.dbo.spt_values;

set identity_insert #test1 on;

The specific problem is that select needs to return something, and that something can't be a non-value for a non-column. And the alternative values syntax only inserts one row at a time.

You might be better off doing:

create table  #test1 (
    test_id int identity(1, 1) primary key,
    dummary varchar(1)
);

insert into #test1 (dummy)
    select top (100) cast(NULL as varchar(1))
    from master.dbo.spt_values;

This uses a minimal amount of space, because a NULL varchar() only occupies space for the NULL bit.

For your code, default values will work:

WHILE @i<@numRows
begin
    INSERT #TEST1 DEFAULT VALUES
    SET @i=@i+1
end

Zohar Peled · Answer 3 · 2018-03-25T09:15:46.937

This option does not require set identity_insert on.
Using merge will allow you to use default values for multiple rows:

MERGE INTO  #TEST1 
USING 
(
    SELECT TOP 100 *
    FROM sys.objects
) S
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES;

(Thanks again to Martin Smith for the merge trick on this SO post)

The advantage here is that this does not assume anything about the identity column, or even the existence of one - it can be used to insert the default value to whatever columns you have on the table, be it a single int identity(1, 1), or even many columns.

You can see a live demo on rextester.

score 1 · Answer 4 · answered Mar 25 '18 at 11:59

1

Or just simply this:

INSERT INTO #Temp1 DEFAULT VALUES
GO 100

Run this in SQL Server Management Studio and you'll get 100 rows inserted into #Temp1

answered Mar 25 '18 at 11:59

marc_s

732,580
175
1,330
1,459

`GO` is just an artificial instruction existing only in SSMS and a few other client tools. Nice for an ad-hoc query, but not for use from a client program. – Alejandro Jun 13 '18 at 16:04
@Alejandro: correct - `GO` is a *batch separator* that works in SSMS and in the SMO libraries. The question also was about SSMS - not about an arbitrary client program ... – marc_s Jun 13 '18 at 16:06
I can't see SSMS being mentioned anywhere in the question nor in its history. – Alejandro Jun 13 '18 at 16:09

score 0 · Answer 5 · answered Mar 25 '18 at 08:35

Use code below :

            //replace server namd and instance with name in login window for SQL Server Management Studio (ssms)
            //replace database name wit you database
            string connectionString = "Server=myServerName\myInstanceName;Database=myDataBase;Trusted_Connection=True;";

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            for (int i = 0; i < 100; i++)
            {
                string query = string.Format("INSERT INTO [dbo].[#TEST1] (TEST_ID) VALUES ({0})", i);
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }

Identity column does not allow direct inserts!! – SSD Mar 25 '18 at 08:42 — SSD, Mar 25 '18 at 08:42

How to insert 100 rows to a single table with only identity column?

5 Answers5