Cannot insert into table with identity column from linked server

Question

I have the following table:

  use db_name;
  create table dbo.tbl_name(
  id_col int identity(1,1),
  col1 varchar(20),
  col2 varchar(50)
  );

into which I can insert values without issue:

insert into dbo.tbl_name
select
col1,
col2
from dbo.other_tbl_name;

The issue arises when I attempt to insert into the table from a linked server:

insert into [server_name].db_name.dbo.tbl_name
select
col1,
col2
from dbo.other_tbl_name;

This gives the following error message:

Column name or number of supplied values does not match table definition

If I try to set identity_insert on so I can include the identity column in the insert I get the error message:

Cannot find the object "server_name.db_name.dbo.tbl_name" because it does not exist or you do not have permissions.

which is somewhat misleading, given that I can select from the table and even issue update statements. I guess I don't have permission to set identity_insert on from the linked server, though I can do it on the server itself directly with the same credentials.

So how can I insert into this table from the linked server?

Answer 1

Explicitly define the columns in your INSERT statement:

insert into [server_name].db_name.dbo.tbl_name (col1,col2)
select
col1,
col2
from dbo.other_tbl_name;