Sqlsever to specify an explicit value for an identity column, most of them are in a table with exactly the same two structure, and one of the tables inserts data from one of the specified criteria in the other table, reporting the following error:
You can specify an explicit value for an identity column in table ' * ' only if the column list is used and Identity_insert is on.
Found a solution on the internet, tried it in person, can solve.
The questions are detailed below:
System: Windows2003
Database: 2005
Database instance: wzgl2004 and wzgl2003, all two instances have table KC
EXECUTE statement
INSERT INTO WZGL2004.DBO.KC select * from WZGL2003.DBO.KC
There is an error message:
"You can specify an explicit value for an identity column in table ' WZGL2004.DBO.KC ' only if the list of columns is used and IDENTITY_INSERT is on. "
Second, the solution:
Copy Code code as follows:
SET Identity_insert Test on
Insert into WZGL2004.DBO.KC (id,name) Select (id,name) from WZGL2003.DBO.KC
SET Identity_insert Test off
Third, the SET identity_insert help knowledge
1, SET IDENTITY_INSERT: Allows explicit values to be inserted into the table's identity column.
2. Grammar
SET Identity_insert [database.[owner.]] {table} {ON | OFF}
Parameters
Database
is the name of the database where the specified table resides.
Owner
is the name of the table owner.
Table
is the name of the table that contains the identity column.
3. Attaching: At any time, the Identity_insert property of only one table in a session can be set to ON. If a table has this property set to ON and a set IDENTITY_INSERT ON statement is issued for another table, Microsoft®sql Server™ returns an error message stating that the SET IDENTITY_INSERT is set to ON and reports This property has been set to on table. If the insertion value is greater than the current identity value of the table, SQL Server automatically uses the new inserted value as the current identity value.
Set IDENTITY_INSERT settings are set at execution or runtime, not at parse time.
Sqlsever specify an explicit value for the identity column this concludes, if you have any questions please contact us www.cncnc.com.cn, China Port provides you with an efficient and secure SQL database.
