Friday, May 27, 2011

How to copy a table from one server to other in SQL Server

Well , there are like 100 ways to do this. But still people face problems in them. I am talking this specific to SQL Server.

CASE 1- You know the admin or sa password of the source server, then you can use SQL Server Import And Export Wizard, which is pretty simple. Here is the explanation, How to: Run the SQL Server Import and Export Wizard

CASE 2-If you don't have sysadmin rights, then the best option is first to create a Linked Server, stackunderflow: How to run a query on Linked Server

And then when you have created the Linked Server, suppose you want to copy a table (with data) PURCHASE_ORDER from your Linked Server (say NVIDIA) and schema (demo) to your local server with schema (demo), use the following query.

SELECT * INTO PURCHASE_ORDER FROM [NVIDIA ].[demo].[dbo].[PURCHASE_ORDER]
GO

How to run a query on Linked Server

For those who don't know what a Linked Server is, it is SQL Server equivalent of DB Links. A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers.

For running a query using Linked Server, you first need to create the linked Server by giving you remote server configurations. For more detailed info on creating it follow the link. How to Create a Linked Server

Once you have created the Linked Server, say with server name NVIDIA and database schema named demo, and now you want to query table PURCHASE_ORDER, then do the following.

SELECT * FROM [NVIDIA ].[demo].[dbo].[PURCHASE_ORDER]
GO

Create a Table Copy in SQL

Many times what we need to do is just want to create a table backup (with data) in our database.

Suppose you have a table named PURCHASE_ORDER, and you need to create a backup of the table. Use the following query.

select * into PURCHASE_ORDER_BAK from PURCHASE_ORDER

The above query will create a backup table PURCHASE_ORDER_BAK and will copy all data from PURCHASE_ORDER to the table. I have tested the above query on SQL Server and I believe same should work on ORACLE as well as MYSQL