Identity_insert on - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity_insert on

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2011-02-09 : 14:45:11

set identity_insert FutureTargetVehicle_CostDetails_Fleet ON
Go
insert into FutureTargetVehicle_CostDetails_Fleet select * from [PC-srividhya\sql_server2008].vinbaseapp2.dbo.FutureTargetVehicle_CostDetails_Fleet

Why am i getting

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'FutureTargetVehicle_CostDetails_Fleet' can only be specified when a column list is used and IDENTITY_INSERT is ON.

the identity_insert is on hence i must be able to insert right?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 14:51:19

you need to explicitly list the columns when using identity_insert

INSERT INTO YourTable(col1, col2, col3)
SELECT col1, col2, col3 FROM otherTable;

AAAV
Posting Yak Master

152 Posts

Posted - 2011-02-09 : 15:38:26

How do i do this then,

i want to copy FutureTargetVehicle_CostDetails_Fleet tables rows from one databse to another with the same table (blank now)

when copying i need to be able to insert values into the identity column also in the destination table

For simplicity let us say

the table name is table1 and the databases are db1 and db2

copy from db1.dbo.table1 to db2.dbo.table1

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 22:18:03

[code]USE db1;
GO
INSERT dbo.table1 (col1, col2, col3...)
SELECT col1, col2, col3...FROM db2.dbo.table2;
GO[/code]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 22:19:33

If db2 is on a linked server:

USE db1;
GO
INSERT dbo.table1 (col1, col2, col3...)
SELECT col1, col2, col3...FROM [PC-srividhya\sql_server2008].db2.dbo.table2;
GO

AAAV
Posting Yak Master

152 Posts

Posted - 2011-02-10 : 10:03:50

There are tables with lot of columns
Is there a way for me to turn off the identity and insert values to that identity column also so that i can use
select * from db2.table1

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-10 : 10:36:19

Just drag the column names out of the object explorer. If you highlight the columns node, you can drag 'em all at the same time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources