XML FeedsHow to insert values into a table with only an identity column
Some databases have something called a sequence, you can use this to generate values like an identity but it can be used accross many tables. In SQL Server you could simulate that by creating a table that has an identity column, inserting into that table and then use the identity generated in your other tables
The reason I am writing this is because I saw the following table
- CREATE TABLE Sequence (ID int identity not null primary key,Dummy tinyint)
- GO
I was thinking to myself why they have that dummy value, so I spoke to one of these people who were using the system. The reason they have this dummy value is so that they can insert into this table like this
- INSERT INTO Sequence VALUES(1)
- INSERT INTO Sequence VALUES(1)
- INSERT INTO Sequence VALUES(1)
And now when you query the table
- SELECT ID
- FROM Sequence
You get these 3 rows as output
ID 1 2 3
So the dummy value is there so that they can insert into the table. Even though the dummy column is not big it still takes up space and it was also nullable which also takes up some space.
Let's do this a different way. First drop the table we created before.
- DROP TABLE Sequence
Now create the table like this; without the dummy value
- CREATE TABLE Sequence (ID int identity not null primary key)
- GO
Here is how the insert looks like if you only have the identity column
- INSERT INTO Sequence DEFAULT VALUES
- INSERT INTO Sequence DEFAULT VALUES
- INSERT INTO Sequence DEFAULT VALUES
- INSERT INTO Sequence DEFAULT VALUES
All you need to use is default values in the insert statements, it will then generate the identity
- SELECT ID
- FROM Sequence
ID 1 2 3 4
Let's take a look at another example, what if we had two other columns and they had defaults on them? First create this table.
- CREATE TABLE Sequence2 (ID int identity not null primary key,
- Somedate datetime default getdate() not null,
- SomeID int default 0 not null)
- GO
Now run these statements
- INSERT INTO Sequence2 DEFAULT VALUES
- INSERT INTO Sequence2 DEFAULT VALUES
- INSERT INTO Sequence2 DEFAULT VALUES
- INSERT INTO Sequence2 DEFAULT VALUES
Now let's look what is in the table
- SELECT * FROM Sequence2
ID Somedate SomeID 1 2010-04-30 12:50:14.693 0 2 2010-04-30 12:50:14.693 0 3 2010-04-30 12:50:14.693 0 4 2010-04-30 12:50:14.693 0
As you can see it used the default values for the columns with defaults and also created the identity values.
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
A nice approach, till now have never used the 
Nice post, Denis. I'd never even heard of Default values until this post. I'll have to bear this in mind for future reference. Thanks! 
awesome.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.