คำสั่งคัดลอกตาราง Sql Server: How to copy a table?

หน้าแรก SQL Server คำสั่งคัดลอกตาราง Sql Server: How to copy a table?

This is the simplest way to copy a table into another (new) table in the same SQL Server database. This way of copying does not copy constraints and indexes.

select * into from

Example:

Select * into employee_backup from employee

========================================================

We can also select only a few columns into the destination table like below

select col1, col2, col3 into from

Example:

Select empId, empFirstName, empLastName, emgAge into employee_backup from employee

========================================================

Use this to copy only the structure of the source table.

select * into from where 1 = 2

Example:

select * into employee_backup from employee where 1=2

========================================================

Use this to copy a table across two database in the same Sql Server.

select * into from

Example:

select * into Mydatabase2.dbo.employee_backup from mydatabase1.dbo.employee

========================================================

Any one of the following methods can be employed to copy a table into a destination database on a different SQL Server.

1. Data Transformation Service (DTS) SQL Server 2000.

2. SQL Server Integration Service (SSIS) SQL Server 2005

3. SQL Server Export Data task. SQL Server 2000/2005

4. Create a linked Server of the destination SQL Server on the source SQL Server and then copy the table. SQL Server 2000/ 2005.

5. We can also use sp_generate_inserts to generate data insertion scripts and then run the insert scripts.

6. I almost forgot this you can open the source table , select the row(s), copy (ctrl + C) the row(s), open the destination table and then paste (ctrl + V) the row(s).

refer: http://cavemansblog.wordpress.com/2009/06/09/sql-server-how-to-copy-a-table/

ขึ้นไปด้านบน