using insert into to append to existing table in a remote database

my goal is to select items from a table and append those items into another ta开发者_如何转开发ble located on a remote database on the same server. All columns in both tables match up and are identical. In this case,

I have the tsql:

INSERT INTO db1.dbo.tblitems 
     SELECT * 
       FROM db2.dbo.tblitems i2 
      WHERE i2 = 'import'

i get an error saying:

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

any ideas why this doesn't work? thanks in advance

Sounds like there is an identity column in the table. An identity column is a column that is made up of values generated by the database. For example:

create table #TestTable (id int identity, name varchar(50))
insert into #TestTable select 1, 'Will Smith'

This gives the identity column error. You can avoid that in two ways: the first is not to insert the identity column, like:

insert into #TestTable (name) select 'Will Smith'

The second is to use set identity_insert (requires admin privileges):

set identity_insert #TestTable on
insert into #TestTable (id, name) select 1, 'Will Smith'
set identity_insert #TestTable off

In both cases, you have to specify the column list.

I agree with Andomar but a further consideration...

Have you considered the effects of merging these two data sets?

Say I had two identical tables in two databases with this data:

Id    Name
1     Bill
2     Bob
3     Bert

Id    Name
3     Jenny
4     Joan
5     Jackie

Option 1 of Andomar's would give the girls new IDs. If that ID has been used as a primary key in the table and other tables referenced it as a foreign key then this will break the referential integrity (you will have records pointing to the wrong place).

Option 2 would fall over if there is a unique index on the ID column, which quite likely if it is being used as a key. This is because the two ID values for Bert and Jenny are not unique.

So while Andomar is right in that it will fix the identity insert problem, it doesn't address the issue of why there were identity columns in the first place.

p.s. if this is an issue ask for a solution in a new question.

This might be an issue of permissions. As the server the query is running on cannot determine if the connected user has the permission to insert the data into the destination server/table, it just might not be possible.