Copying a table across linked servers

This procedure will copy a table across linked servers, overwriting the destination table. It is a time-saver in mixed server development, and is especially useful in keeping development data in sync with production data in remote servers. To use it, compile the procedure std_synctable and run it with the following arguments:

source server name, 
source database name, 
source table name, 
destination table name, 
remote sqluser name, 
remote sqlpassword 

Note: if the remote server has not been registered, either do it from the Enterprise Manager or uncomment the related code in the procedure and execute it once (see Note 1 in code).

                
CREATE PROCEDURE dbo.std_synctable 

@source_server varchar(50), 
@source_database varchar(50), 
@source_table varchar(50), 
@dest_table varchar(100), 
@sqluser varchar(30), 
@sqlpassword varchar(30) 

AS 
BEGIN 
declare @db varchar(50) 
select @db = db_name() 
EXEC('USE master') 
EXEC ("sp_dboption " + @db + ", 'ANSI_NULLS', 'TRUE'") 
EXEC ("sp_dboption " + @db + ", 'ANSI_WARNINGS', 'TRUE'") 
EXEC('USE ' + @db) 

--std_synctable 
--Created 8/8/2001 by Jorge 
--Last modified: 8/9/2001 (fixed ANSI_NULLS and 
--ANSI_WARNINGS options set) 
--Copies a table from a remote server 
--Arguments are: 
--source server, source database, source table, 
--destination table, userid, userpassword 
--(from the remote server) 
--Note 1: 
--If the remote server has not been added as a linked server, 
--run this once: 
------------------------------ 
--EXEC sp_addlinkedserver 
--   @server = 'SERVERNAME', 
--   @srvproduct = 'SQL Server', 
--   @provider = 'MSDASQL', 
--   @provstr = 'DRIVER={SQL Server};SERVER=SERVERNAME;
--               UID=username;PWD=password;' 
--GO 
------------------------------- 
--now connect as the linked server user 
--EXEC sp_addlinkedsrvlogin @source_server, 'false', NULL, 
[email protected], @sqlpassword 

declare @sql varchar(500) 
declare @source varchar(200) 
select @source = '[' + @source_server + '].' 
       + @source_database + '.dbo.' + @source_table 

--first drop the destination table here 
IF OBJECT_ID(@dest_table) IS NOT NULL 
BEGIN 
    select @sql = 'DROP TABLE ' + @dest_table 
    EXEC(@sql) 
END 

--now copy the data from the remote server: 
select @sql = "select * INTO " + @dest_table + 
              ' FROM ' + @source 
EXEC(@sql) 

END 
GO 

For More Information

• What do you think about this tip? E-mail us at [email protected] with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.