Results 1 to 4 of 4

Thread: Remote insert

  1. #1
    Join Date
    Jun 2005
    Posts
    44

    Unanswered: 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

    Code:
    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    News to me. I'll try it out when I get to my office.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    44
    Thanks blindman

  4. #4
    Join Date
    Jul 2006
    Posts
    1
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •