Inserting into a table with only identity column

Question

Post reply

Inserting into a table with only identity column

JG-324908

SSC-Addicted

Points: 432
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/