Remote Insert

I have an issue i need to understand and have not been able to find an answer yet. 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 references a local table 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?

To test this i've supplied some very simple code. Just create source tbl on local server and destination and log tbl on remote server. Setup a linked server and run the remote insert then do a local insert and look at the log.

All advise gratefully received!

Cheers


Andrew


CREATE TABLE [dbo].[source] (
[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 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 source table
insert into source values ('MYSERVER','1/1/2000')
insert into source values ('MYSERVER','1/2/2000')
insert into source values ('MYSERVER','1/3/2000')

--Run the insert from the source db

insert into destinationsrv.destdb.dbo.destination
select * from source

--Switch to the destination server and run select there is only meant to be 1 row!
select * from tbllog