# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Turning IDENTITY_INSERT ON

## Matt

Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT * 
	FROM  	dbo.TABLE1
		dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table &#39;DB1.dbo.TABLE&#39; can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt

----------


## Kurt

Matt,
First, are you sure you want to insert identity values into your table?  If you do you can use the Set Identity_Insert statment.

You would use it just before you insert statement in the same batch:

Set Identity_Insert db1.dbo.table1 ON

Then do your insert.


------------
Matt at 8/22/01 4:40:23 PM


Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT * 
	FROM  	dbo.TABLE1
		dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table &#39;DB1.dbo.TABLE&#39; can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt

----------


## Kurt

Matt,
First, are you sure you want to insert identity values into your table?  If you do you can use the Set Identity_Insert statment.

You would use it just before you insert statement in the same batch:

Set Identity_Insert db1.dbo.table1 ON

Then do your insert.


------------
Matt at 8/22/01 4:40:23 PM


Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT * 
	FROM  	dbo.TABLE1
		dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table &#39;DB1.dbo.TABLE&#39; can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt

----------

You have to accomplish two conditions:

1) set identity insert to ON:
SET IDENTITY_INSERT db1.dbo.table1 ON

2) specify column list, for example:
INSERT INTO
DB1.dbo.TABLE (column1, column2, column3)
SELECT column1, column2, column3
	FROM  	dbo.TABLE1
		dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

Marcel

------------
Matt at 8/22/01 4:40:23 PM


Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT * 
	FROM  	dbo.TABLE1
		dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table &#39;DB1.dbo.TABLE&#39; can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt

----------

