Thursday, January 16, 2014

how to copy table sql server from one database to another with data

The simplest way to copy table from one database to another, you do not have to generate table script or create table manually.

This way of copying does not copy constraints and indexes.


select * into <destination table> from <source table>
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 <destination table>
from <source table>
Example:
Select empId, empFirstName, empLastName, emgAge into employee_backup
from employee

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

select * into <destination table> from <source table> 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 <destination database.dbo.destination table>
from <source database.dbo.source table>
Example:
select * into Mydatabase2.dbo.employee_backup
from mydatabase1.dbo.employee

No comments:

Post a Comment