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, [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.