Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
IDENTITY columns in SQL Server (Read 3038 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
IDENTITY columns in SQL Server
18.02.10 at 10:46:05
 
IDENTITY columns in SQL Server
 
This article is written while exploring the IDENTITY property of SQL Server database columns.
 
To create a table with an edentity column the following SQL can be used:
(http://msdn.microsoft.com/en-us/library/aa933196%28SQL.80%29.aspx)
 
CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
)
 
INSERT new_employees
  (fname, minit, lname)
VALUES
  ('Karin', 'F', 'Josephs')
 
INSERT new_employees
  (fname, minit, lname)
VALUES
  ('Pirkko', 'O', 'Koskitalo')
Back to top
 
« Last Edit: 18.02.10 at 10:46:36 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Cannot insert explicit value for indentity column
Reply #1 - 18.02.10 at 10:48:43
 
Cannot insert explicit value for indentity column
 
Inserting a value into an Identity column givs the following error message:
 
Cannot insert explicit value for identity column in table 'new_employees' when IDENTITY_INSERT is set to OFF.
 
Values can be inserted after issueing the following set command. You can run these in SQL*XL by making use of the exec statement. Run the following statement in the SQL*XL SQL dialog:
 
exec set IDENTITY_INSERT new_employees ON;
 
When inserting into a table after setting the IDENTITY_INSERT property to ON you may get the following error if you omit the value for the identity column:
 
Explicit value must be specified for identity column in table 'new_employees' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
 
Newly inserted values are considered when continuing with the automatic incrementing of the identity column. When values 1 and 2 were issed by SQL Server and when a value of 3 was manually added, SQL Server continues with 4.
Back to top
 
« Last Edit: 18.02.10 at 11:16:24 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1