Inserting into a table with only identity column

Question

Post reply

Inserting into a table with only identity column

JG-324908

SSC-Addicted

Points: 462
More actions
September 29, 2008 at 3:23 am

#198919

Hi All,
Your help is needed,
Consider a temporary table as
CREATE TABLE #test (test_id INT IDENTITY(1,1))
with only identity column in it.
Qn: How can I write INSERT query to populate #test table with 20 rows?
I got to know that we can use
INSERT INTO #test DEFAULT VALUES
to populate one record.
Is there any way that I can populate 20 records from a single query?
Thanks in advance,
Jagga

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Unless there's a very good reason to perform this task in two separate steps, use only one:

[font="Courier New"]SELECT TOP 20 IDENTITY(INT,1,1) AS test_id

INTO #test

FROM MASTER.dbo.syscolumns sc1,

MASTER.dbo.syscolumns sc2

SELECT * FROM #test

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

If you are using Managemant studio, you can just type GO 20 after the insert statement:

INSERT INTO #test DEFAULT VALUES

go 20

This will cause the insert statement to run 20 times. If you need it in a procedure, then you can just run it in a loop:

DECLARE @I INT

SET @I = 1

WHILE @I <= 20

BEGIN

INSERT INTO #test DEFAULT VALUES

SET @I = @I + 1

END

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JG-324908 SSC-Addicted Points: 462 More actions · Answer 3

Thanks Chris, but this will create new table not inserting into a existing table.

Regards

JG-324908 SSC-Addicted Points: 462 More actions · Answer 4

Thanks Adi,

This is what I was looking for.

Thanks for the answer.