Copying whole rows between SQL Servers

[ptj]

I need to copy chunks of information from one SQL server to another. One is on a SQL 2000 server, the other is MSDE 2000 sitting on a local machine. I need to be able to copy whole rows at a time, but not whole tables. At the moment I have a rather messy solution where I create two recordsets, one linked to each database and copy info between them. I have created two connections in VB and what I would like is a SQL command that does something like:

COPY WHOLEROW FROM server1.catalog1.tblClient TO server2.catalog2.tblClient WHERE prikey = 7

Is anyone aware of such a command & if it exists does it matter that the servers are on different connections?

Would this question be more appropriately posted in a VB forum?

Thanks in anticipation


Phil

 

[tlbroadbent]

In MSDE, add a linked server for the SQL Server 2000. Then create a stored procedure in the database in MSDE that will insert rows into the MSDE table from the server.

INSERT database.owner.tblClient
SELECT * FROM server1.database.owner.tblClient
WHERE prikey = 7 If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm