13

I have a table where all columns are auto-populated whenever an insertion happens:

CREATE TABLE …
(
    ItemID      INT       NOT NULL IDENTITY(…),
    DateCreated DATETIME2 NOT NULL DEFAULT GETDATE()
);

How do I write a SQL statement that inserts a new row into this table without having to manually provide any concrete values to insert?

(There is already a similar question, but it differs in that it's about a table with some non-DEFAULT columns for which a value must be manually provided.)

Community
  • 1
  • 1
volume one
  • 6,800
  • 13
  • 67
  • 146
  • As an honest question, what use is such a table? – hunch_hunch Sep 17 '14 at 21:56
  • The table is used to hold ItemIDs which are used in other tables as a Foreign Key. For example a Photo is an item, a Video is an item etc. The ItemID table is required to keep a unique ID across multiple tables. So when someone inserts a new Photo, it firsts adds a row to the table mentioned in my question which auto-generates a unique value for ItemID. This auto-generated value is inserted into the Photo table as an FK. – volume one Sep 17 '14 at 21:57
  • 1
    Alternative: Use a `SEQUENCE` to generate unique key values across several tables. It's a feature new to SQL Server 2012 and has some issues of its own (similar to `IDENTITY`), but requires less storage space than an extra table. – stakx - no longer contributing Sep 17 '14 at 22:05
  • @stakx thanks for the edit. I didn't know about SEQUENCE (only heard of it) but if I do use that, then how will I have a corresponding DateTime value for when that value was created? Hence I probably do still need a table – volume one Sep 17 '14 at 22:18
  • 1
    @volumeone: Both the ID and its creation date would appear in the various item tables. `CREATE SEQUENCE ItemId …; CREATE TABLE BlobItems (ItemId INT NOT NULL DEFAULT NEXT VALUE FOR ItemId, DateCreated DATETIME2 NOT NULL DEFAULT GETDATE(), …);` Every one of your item tables would have identical column definitions for `ItemId` and `DateCreated`. If that's not what you want, then you're probably better off with your current schema. – stakx - no longer contributing Sep 17 '14 at 22:26
  • 1
    [and to insert multiple rows](http://stackoverflow.com/a/12863711/73226) – Martin Smith Sep 17 '14 at 22:37

1 Answers1

25

Use the DEFAULT VALUES option:

INSERT INTO IdentitySpecification
DEFAULT VALUES;
D Stanley
  • 149,601
  • 11
  • 178
  • 240