Behaviour issue with remote inserts

I have an issue doing remote inserts and don't understand why there is a behaviour difference between local and remote inserts. So far i have not been able to find an

answer. It may be something to do with parametised query execution

but i`m not sure yet.

Below is the scenario

If i do a

insert into server.db.dbo.remotetable

select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the

destination server where as if the insert into is local only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert? Its currently playing havoc with a trigger i have on a production box.

To test this i've supplied some very simple code. Setup instructions are commented into the code. Have not coded a linked server creation though.

At the end look at the tbllog and you will see what i mean.

All advise gratefully received!

Cheers
Andrew

Sample Code

---

--CREATE this table on Source server and Destination server

CREATE TABLE [dbo].[Input] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create this table on the destination table on destination server
CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the input table on Source server
insert into input values ('Remote','1/1/2000')
insert into input values ('Remote','1/2/2000')
insert into input values ('Remote','1/3/2000')

--Insert some sample data into the input table on Destination server
insert into input values ('Local','1/1/2000')
insert into input values ('Local','1/2/2000')
insert into input values ('Local','1/3/2000')

--Run the insert from the source server
insert into destinationsrv.destdb.dbo.destination
select * from input

--Run the insert from the destination server (as a local query)
insert into dbo.destination
select * from input

--On the destination server, run select from the log table which was populated by trigger.
--You will see remote insert has 3 rows but local only has 1!
select * from tbllogHmmm. Looks like SQL Server uses cursors to run any remote query, even if it is run via OPENROWSET (just tested it). I wonder if there is a way to avoid this, except moronic workarounds like temp table & remote SP.|||I've been hunting high and low for a way to get it to behave the same way as a local insert and not do multiple inserts without resorting to temp tables and remote sp's. Was hoping there might even have been a reg setting but not found one.

I'd also like to understand why it has to break down into multiple inserts!

Still looking for the answer but no joy. |||

I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?