select * into <destination table> from <source table>
Example:
Select * into employee_backup from employee
我们也可以只拷贝某些字段:
select col1, col2, col3 into <destination table>
from <source table>
Example:
Select empId, empFirstName, empLastName, emgAge into employee_backup
from employee
下面的方法仅拷贝表结构,不包含数据:
select * into <destination table> from <source table> where 1 = 2
Example:
select * into employee_backup from employee where 1=2
而下面方法可将表拷贝到另外的 SQL Server 服务器上:
select * into <destination database.dbo.destination table>
from <source database.dbo.source table>
Example:
select * into Mydatabase2.dbo.employee_backup
from mydatabase1.dbo.employee
只有满足下面条件才能将表拷贝到其他 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. 在源 SQL Server 上创建一个链接到远程服务器的连接 – SQL Server 2000/ 2005.
5. 我们也可使用 sp_generate_inserts 来生成数据插入脚本然后在远程运行脚本
6. 另外,你也可以打开表然后复制粘贴的目标表