x

Identity Insert Statement

I have participated in several of the StackOverflow sites, and one of the things that I have noticed is that there is a real "bootstrap" problem in the beginning. There really needs to be a certain minimum number of active members with a minimum number of points before it really starts to work. In the interests of addressing this, (and unless the powers that be object) I will be posting challenge questions based on some of the older SqlServerCentral Questions Of The Day. The first answer which is correct will be marked as accepted (15 points). For the most part I will not be presenting the questions as multiple choice, as they originally were.


Identity Insert:

We have a table and it has just one column that the identity property of that column is set to ON, we need a query to insert some records in that, which of the following will work:

We have the following table:

CREATE TABLE TestID( ID int Identity(1,1))

What is the INSERT statement to insert records into this table?

more ▼

asked Oct 10 '09 at 01:46 AM in Default

RBarryYoung gravatar image

RBarryYoung
782 5 5 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
INSERT INTO TestID DEFAULT VALUES 
more ▼

answered Oct 10 '09 at 08:37 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Great Job, Matt! You've almost got enough points now to down-vote things (though I would recommend against that during this break-in period for the site, except the most awful stuff, spam, ads, etc.)
Oct 10 '09 at 10:55 AM RBarryYoung
I thoroughly agree - I think it's going to be down to some of the SSC residents to build the site up, looking forward to it being more active :)
Oct 10 '09 at 07:34 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

May be worth mentioning that from SQL Server Management Studio (SSMS) you can add an integer after the GO command that inticates how many times to execute the batch. eg:

INSERT INTO TestID DEFAULT VALUES
GO 100
Will enter 100 rows into the table. No need to create a loop. And if you use Random numbers, GETDATE(), joins or similar functions you can rapidly build halfway decent load tables.
more ▼

answered Aug 07 '10 at 05:32 PM

David Lean gravatar image

David Lean
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103
x33
x14
x4

asked: Oct 10 '09 at 01:46 AM

Seen: 1652 times

Last Updated: Oct 11 '09 at 06:13 AM