Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – COPY TABLE from another SQL Server

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1\SQL_SERVER_INSTANCE_1',@useself=N'False',@locallogin=NULL,@rmtuser=N'MY_MIGRATION',@rmtpassword='########'
GO

 2)   On the destination server

 

--SELECT * into  from   

--Example:  Using the Linked server

SELECT * INTO my_test_table_copy FROM [SERVER1\SQL_SERVER_INSTANCE_1].MY_DB.dbo.my_test_table

 

See Also

SQL SELECT INTO

SQL Server Linked Servers and User permissions

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...