Insert row into table with only one column, primary key and identity

Question

Here is a table definition:

CREATE TABLE dbo.TableOnlyPK
(
    Id tinyint PRIMARY KEY IDENTITY (1, 1)
)

Now I need to insert row into that table through T-SQL statements: I've tried few solutions, but no one worked.

INSERT INTO dbo.TableOnlyPK () VALUES ()  -- not worked

Is there any construction that will force SQL to alone write new PK into table. I know that it is feasible with IDENTITY_INSERT, but I am wondered if it is possible without that statement. — veljasije
– veljasije, Commented May 11, 2015 at 10:12
You should indicate columns names and/or values and besides you should know that the pk column should be ignored if it is an identity field — Beatles1692
– Beatles1692, Commented May 11, 2015 at 10:13
If a column is IDENTITY, then you cannot insert user value in that column (without IDENTITY_INSERT) — veljasije
– veljasije, Commented May 11, 2015 at 10:14
@Zohar - This can have numerous usages. One might be as a general sequence / latest ID across an entire application. — fdomn-m
– fdomn-m, Commented May 11, 2015 at 10:18
as of sql server 2012 there is a sequence object. I guess it might be useful if you are using an older version. This is what I mean by asking why. if the OP is using 2012 or higher, he might be better of with a sequence. — Zohar Peled
– Zohar Peled, Commented May 11, 2015 at 10:20

Kartic · Accepted Answer · 2015-05-11 10:22:21Z

12

Try:

INSERT INTO dbo.TableOnlyPK DEFAULT VALUES

You have created below table:

CREATE TABLE dbo.TableOnlyPK
(
    Id tinyint PRIMARY KEY IDENTITY (1, 1)
)

Each time you fire : INSERT INTO dbo.TableOnlyPK DEFAULT VALUES, you will insert one row in IDENTITY column.

So if you execute:

INSERT INTO dbo.TableOnlyPK DEFAULT VALUES
INSERT INTO dbo.TableOnlyPK DEFAULT VALUES
INSERT INTO dbo.TableOnlyPK DEFAULT VALUES

It will result:

enter image description here

answered May 11, 2015 at 10:14

Kartic

2,9835 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bravo! That is what I need

fdomn-m · Accepted Answer · 2024-03-14 18:17:02Z

3

To insert an "empty" record, you can use DEFAULT VALUES where the columns will use any defined DEFAULT, or, in this case the IDENTITY

INSERT INTO [dbo].[TableOnlyPK]
DEFAULT VALUES

answered May 11, 2015 at 10:14

fdomn-m

28.8k8 gold badges39 silver badges70 bronze badges

Bravo! That is what I need