Copy tables from one database to another in SQL Server

Question

I have a database called foo and a database called bar  I have a table in foo called tblFoobar that I want to move  data and all  to database bar from database foo  What is the SQL statement to do this

User · Answer

You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script s that can do the following       copy the table schema any constraints  identity  default values  etc  data within the table and many other options if needed     Good example workflow for SQL Server 2008 with screen shots shown here

User · Answer

Script the create table in management studio  run that script in bar to create the table   Right click table in object explorer  script table as  create to     INSERT bar  schema  table SELECT   FROM foo  schema  table

User · Answer

On SQL Server  and on the same database server  Use three part naming   INSERT INTO bar  tblFoobar   fieldlist    SELECT  fieldlist  FROM foo  tblFoobar   This just moves the data   If you want to move the table definition  and other attributes such as permissions and indexes   you ll have to do something else

User · Answer

If there is existing table and we wants to copy only data  we can try this query   insert into Destination Existing Tbl select col1 col2 FROM Source Tbl

User · Answer

SQL Server Management Studio s  Import Data  task  right-click on the DB name  then tasks  will do most of this for you   Run it from the database you want to copy the data into   If the tables don t exist it will create them for you  but you ll probably have to recreate any indexes and such   If the tables do exist  it will append the new data by default but you can adjust that  edit mappings  so it will delete all existing data   I use this all the time and it works fairly well

User · Answer

You may go with this way    a general example   insert into QualityAssuranceDB dbo Customers  columnA  ColumnB  Select columnA  columnB from DeveloperDB dbo Customers  Also if you need to generate the column names as well to put in insert clause  use      select  name        as TableColumns from sys columns  where object id   object id  YourTableName    Copy the result and paste into query window to represent your table column names and even this will exclude the identity column as well      select  name        as TableColumns from sys columns  where object id   object id  YourTableName   and is identity   0  Remember the script to copy rows will work if the databases belongs to the same location   You can Try This  select   into  lt Destination table gt  from  lt Servername gt   lt DatabaseName gt  dbo  lt sourceTable gt   Server name is optional if both DB is in same server

User · Answer

On SQL Server  and on the same database server  Use three part naming   INSERT INTO bar  tblFoobar   fieldlist    SELECT  fieldlist  FROM foo  tblFoobar   This just moves the data   If you want to move the table definition  and other attributes such as permissions and indexes   you ll have to do something else

User · Answer

SQL Server Management Studio s  Import Data  task  right-click on the DB name  then tasks  will do most of this for you   Run it from the database you want to copy the data into   If the tables don t exist it will create them for you  but you ll probably have to recreate any indexes and such   If the tables do exist  it will append the new data by default but you can adjust that  edit mappings  so it will delete all existing data   I use this all the time and it works fairly well

User · Answer

This should work  SELECT    INTO DestinationDB  MyDestinationTable  FROM SourceDB  MySourceTable   It will not copy constraints  defaults or indexes  The table created will not have a clustered index  Alternatively you could  INSERT INTO DestinationDB  MyDestinationTable  SELECT   FROM SourceDB  MySourceTable  If your destination table exists and is empty

User · Answer

Copy Data   INSERT INTO Alfestonline  url details url  status  recycle   SELECT url status recycle FROM AlfestonlineOld  url details

User · Answer

On SQL Server  and on the same database server  Use three part naming   INSERT INTO bar  tblFoobar   fieldlist    SELECT  fieldlist  FROM foo  tblFoobar   This just moves the data   If you want to move the table definition  and other attributes such as permissions and indexes   you ll have to do something else

User · Answer

This should work  SELECT    INTO DestinationDB  MyDestinationTable  FROM SourceDB  MySourceTable   It will not copy constraints  defaults or indexes  The table created will not have a clustered index  Alternatively you could  INSERT INTO DestinationDB  MyDestinationTable  SELECT   FROM SourceDB  MySourceTable  If your destination table exists and is empty

User · Answer

SQL Server Management Studio s  Import Data  task  right-click on the DB name  then tasks  will do most of this for you   Run it from the database you want to copy the data into   If the tables don t exist it will create them for you  but you ll probably have to recreate any indexes and such   If the tables do exist  it will append the new data by default but you can adjust that  edit mappings  so it will delete all existing data   I use this all the time and it works fairly well

User · Answer

This should work  SELECT    INTO DestinationDB  MyDestinationTable  FROM SourceDB  MySourceTable   It will not copy constraints  defaults or indexes  The table created will not have a clustered index  Alternatively you could  INSERT INTO DestinationDB  MyDestinationTable  SELECT   FROM SourceDB  MySourceTable  If your destination table exists and is empty

User · Answer

If there is existing table and we wants to copy only data  we can try this query   insert into Destination Existing Tbl select col1 col2 FROM Source Tbl

User · Answer

SQL Server Management Studio s  Import Data  task  right-click on the DB name  then tasks  will do most of this for you   Run it from the database you want to copy the data into   If the tables don t exist it will create them for you  but you ll probably have to recreate any indexes and such   If the tables do exist  it will append the new data by default but you can adjust that  edit mappings  so it will delete all existing data   I use this all the time and it works fairly well

User · Answer

Copy Data   INSERT INTO Alfestonline  url details url  status  recycle   SELECT url status recycle FROM AlfestonlineOld  url details

User · Answer

This should work  SELECT    INTO DestinationDB  MyDestinationTable  FROM SourceDB  MySourceTable   It will not copy constraints  defaults or indexes  The table created will not have a clustered index  Alternatively you could  INSERT INTO DestinationDB  MyDestinationTable  SELECT   FROM SourceDB  MySourceTable  If your destination table exists and is empty

User · Answer

You may go with this way    a general example   insert into QualityAssuranceDB dbo Customers  columnA  ColumnB  Select columnA  columnB from DeveloperDB dbo Customers  Also if you need to generate the column names as well to put in insert clause  use      select  name        as TableColumns from sys columns  where object id   object id  YourTableName    Copy the result and paste into query window to represent your table column names and even this will exclude the identity column as well      select  name        as TableColumns from sys columns  where object id   object id  YourTableName   and is identity   0  Remember the script to copy rows will work if the databases belongs to the same location   You can Try This  select   into  lt Destination table gt  from  lt Servername gt   lt DatabaseName gt  dbo  lt sourceTable gt   Server name is optional if both DB is in same server

User · Answer

If it   s one table only then all you need to do is    Script table definition Create new table in another database  Update rules  indexes  permissions and such Import data  several insert into examples are already shown above    One thing you   ll have to consider is other updates such as migrating other objects in the future  Note that your source and destination tables do not have the same name  This means that you   ll also have to make changes if you dependent objects such as views  stored procedures and other    Whit one or several objects you can go manually w o any issues  However  when there are more than just a few updates 3rd party comparison tools come in very handy   Right now I   m using ApexSQL Diff for schema migrations but you can   t go wrong with any other tool out there

User · Answer

Script the create table in management studio  run that script in bar to create the table   Right click table in object explorer  script table as  create to     INSERT bar  schema  table SELECT   FROM foo  schema  table

User · Answer

You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script s that can do the following       copy the table schema any constraints  identity  default values  etc  data within the table and many other options if needed     Good example workflow for SQL Server 2008 with screen shots shown here

User · Answer

Script the create table in management studio  run that script in bar to create the table   Right click table in object explorer  script table as  create to     INSERT bar  schema  table SELECT   FROM foo  schema  table

User · Answer

Script the create table in management studio  run that script in bar to create the table   Right click table in object explorer  script table as  create to     INSERT bar  schema  table SELECT   FROM foo  schema  table

User · Answer

On SQL Server  and on the same database server  Use three part naming   INSERT INTO bar  tblFoobar   fieldlist    SELECT  fieldlist  FROM foo  tblFoobar   This just moves the data   If you want to move the table definition  and other attributes such as permissions and indexes   you ll have to do something else

User · Answer

If it   s one table only then all you need to do is    Script table definition Create new table in another database  Update rules  indexes  permissions and such Import data  several insert into examples are already shown above    One thing you   ll have to consider is other updates such as migrating other objects in the future  Note that your source and destination tables do not have the same name  This means that you   ll also have to make changes if you dependent objects such as views  stored procedures and other    Whit one or several objects you can go manually w o any issues  However  when there are more than just a few updates 3rd party comparison tools come in very handy   Right now I   m using ApexSQL Diff for schema migrations but you can   t go wrong with any other tool out there

[sql] Copy tables from one database to another in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to copy

Examples related to migrate

Examples related to database-table