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.