loading..
Русский    English
04:31

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

  1. INSERT INTO test_Identity VALUES(DEFAULT);
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;
  2. 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?


Bookmark and Share
Pages 1 2 3 4
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100