15:05

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Adding rows to a table with identity column page 2

Let's consider a table with a single auto-increment column (SQL Server):

CREATE TABLE test_Identity(
    id int IDENTITY(1,1) PRIMARY KEY
);

How to insert rows into above table? If you'll try to not specify a value

INSERT INTO test_Identity VALUES();

or to use DEFAULT keyword

INSERT INTO test_Identity VALUES(DEFAULT);

you'll not succeed - error is answer.

Surely, we could insert specific value when turning the counter off:

SET IDENTITY_INSERT test_Identity ON;
INSERT INTO test_Identity(id) VALUES(5);
SELECT * FROM test_Identity;

but this has no sense for us.

The task we are trying to solve is being worded as: "How to insert namely successive values of a counter into a table?" It turns out that answer is obvious, it lies in Standard syntax:

SET IDENTITY_INSERT test_Identity OFF;
INSERT INTO test_Identity DEFAULT VALUES;

It is hardly imagined that you'll use DEFAULT VALUES in other situations, as existence of primary key allow you to use that clause only once when inserting default values for all the columns of table. But here we can rerun the above statement as many times as many successive values we need in a table.

But what about other DBMS?

Pages

DEFAULT VALUES IDENTITY IDENTITY_INSERT

Previous [INSERT statement]

[Table-values constructor] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
several days ago

SQL Problems and solutions

S. I. Moiseenko

Data modification statements

INSERT statement

Adding rows to a table with identity column page 2