Problem solve Get help with specific problems with your technologies, process and projects.

Copying a table across linked servers

This procedure will copy a table across linked servers, overwriting the destination table.

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, 
--@sqluser, @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 editor@searchDatabase.com 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.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close