Русский    English

Adding rows to a table with identity column page 2

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

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

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

  1. INSERT INTO test_Identity VALUES();
or to use DEFAULT keyword

you'll not succeed  - error is answer.

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

  1. SET IDENTITY_INSERT test_Identity ON;
  2. INSERT INTO test_Identity(id) VALUES(5);
  3. 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:

  1. SET IDENTITY_INSERT test_Identity OFF;

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?

Bookmark and Share
Pages 1 2 3 4
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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.