Yongpil


Hi there,

I wrote a script to copy data from server1(epxress) to server2(2005 standard)

SET identity_insert results on

GO

insert [server2].TEST.dbo.results

select * from [server1].TEST.dbo.results

results table are identical with primary keys.

Problem is when run this code it gives an error

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'dbo.results' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I need a help to set it so when it tries to copy the existing row (same primary key), it ignores it and moves on to the next row..

Thanks guys!





Re: insert problem with primary key

Kent Waldrop Oc07


I think what it is telling you is that you need to be in the format

Code Block

SET identity_insert results on

GO

insert [server2].TEST.dbo.results

(column_1, Column_2, ... Column_N)

select * from [server1].TEST.dbo.results

in order to do this insert. Will someone please check me on this






Re: insert problem with primary key

Mike Kim

Thanks Kent,

However I've already tried that and the error msg is

Msg 4122, Level 16, State 1, Line 4

Remote table-valued function calls are not allowed.

I'm pretty sure there has to be another way....

anyone







Re: insert problem with primary key

NorthwindDBA

Kent Waldrop Oc07 wrote:

I think what it is telling you is that you need to be in the format

Code Block

SET identity_insert results on

GO

insert [server2].TEST.dbo.results

(column_1, Column_2, ... Column_N)

select * from [server1].TEST.dbo.results

in order to do this insert. Will someone please check me on this

Yep, this is the way it should be done.

don't forget to add...

SET identity_insert results off






Re: insert problem with primary key

Mike Kim

thanks for you reply,

but then why am I getting an error when I do that

and what does that error mean

thanks






Re: insert problem with primary key

NorthwindDBA

Mike Kim wrote:

Thanks Kent,

However I've already tried that and the error msg is

Msg 4122, Level 16, State 1, Line 4

Remote table-valued function calls are not allowed.

I'm pretty sure there has to be another way....

anyone

Use DTS or BCP.

Or, the work around is to connect to server2 first.

select * INTO server1_results_temp from [server1].TEST.dbo.results

then do the identity insert while on the same database.






Re: insert problem with primary key

Manivannan.D.Sekaran

Other Possible solutions without using temp table,

Using sp_executesql:

Code Block

--Execute On Server2

SET IDENTITY_INSERT results ON

insert [server2].TEST.dbo.results (column_1, Column_2, ... Column_N)

Exec [server1].TEST.dbo.sp_executesql N'Select * from results'

SET IDENTITY_INSERT results OFF

Using OPENROWSET:

Code Block

--Execute on Server2

SET IDENTITY_INSERT results ON

insert [server2].TEST.dbo.results (column_1, Column_2, ... Column_N)

select * from

openrowset

(

'SQLNCLI',

'SERVER=server1;UID=sa;PWD=sqladmin',

'SET NOCOUNT ON;SET FMTONLY OFF;Select * from test.dbo.results'

);

SET IDENTITY_INSERT results OFF






Re: insert problem with primary key

Manivannan.D.Sekaran

SET IDENTITY_INSERT RESULTS OFF

NOTE:

IDENTITY_INSERT is scope specific. If you forget to turn it OFF, it wonĄŻt affect for otherĄŻs scope or session.

But, as per the best practice if you created & owned it, delete yourself when it is not required ¨C rather allowing the system do this operation. (the same rule is applicable on temp tables also).






Re: insert problem with primary key

Ian Roke

It would be much easier and simpler to move the data across using SSIS. (SQL Server Integration Services)





Re: insert problem with primary key

Manivannan.D.Sekaran

I partially agree with you,

Here the issue is inserting remote server data into IDENITITY column, even if you use SSIS/DTS the same error you will get it. Again you have to use the temporary tables to hold your data.

Am I Rite






Re: insert problem with primary key

IngJVV

My solution is

first veryfy is one or more field is identity, une field identity is auto-inser no need insert this field.

Try