By commenting, you are accepting the DISQUS terms of service.
In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.
SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process.
In order to go through each one of these methods, we will consider the below scenario: The hosting SQL Server: localhost. The tables that will be copied from the source database to the destination one are: Department, Employee , Employee Department History and Employee Pay History under the Human Resources schema.
Both databases hosted in the same SQL Server 2014 instance. In order to copy our tables from the Adventure Works2012 database to the SQLShack Demo one, we can use the SELECT INTO SQL statement.
This statement will create the tables in the destination database first, then it will copy the data to these tables.
This database was cobbled together over a number of years, and it started off serving a totally different purpose than it serves now. [web page table]page_id (autonumber)page_topic (text)... [topics table]topic_id (autonumber)topic_name (text)... Right now, the tables are joined by the two text columns (page_topic = topic_name) which is not ideal.
This is valid in most cases unless the SELECT statement contains JOIN, you are using UNION to join multiple SELECT statements, the IDENTITY column is mentioned many times in your select statement or the source of this IDENTITY column is a remote data source.
If any one of these conditions mentioned is true, the column will be created with NOT NULL property instead of inheriting the required IDENTITY property.
Using this method, the tables’ Indexes and keys will not be transferred.
If you are interested in copying it, you need to generate scripts for these database objects.