How 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