Sql Server

To copy all tables with their data from one database to another database in SQL Server, you can use the following steps:

1. Open SQL Server Management Studio (SSMS) and connect to the source database from which you want to copy the tables.

2. Make sure you are logged in as a user with the necessary permissions to perform the copy.

3. In SSMS, open a new query for the source database.

4. Run the following query to generate CREATE TABLE scripts for all tables in the source database:

SELECT 'SELECT * INTO [DatabaseDestination].[schema].[TableName] FROM [DatabaseSource].[schema].[TableName]'
FROM [DatabaseSource].sys.tables

Be sure to replace `[DatabaseSource]` with the name of the source database and `[DatabaseDestination]` with the name of the destination database.

5. After running the query, you will get a list of SELECT INTO scripts for each table in the source database. Copy these scripts.

6. Now connect to the destination database in SSMS.

7. Open a new query for the destination database.

8. Paste the SELECT INTO scripts copied in the previous step into the destination database query window.

9. Run SELECT INTO scripts to copy data from each table from the source database to the destination database.

Once you have run all SELECT INTO scripts, all tables with their data should be copied from the source database to the destination database.

Be sure to replace `[schema]` with the actual schema of the tables you want to copy, and `[TableName]` with the actual name of each table you want to copy.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi3y ago
To copy all tables with their data from one database to another database in SQL Server, you can use the following steps:

1. Open SQL Server Management Studio (SSMS) and connect to the source database from which you want to copy the tables.

2. Make sure you are logged in as a user with the necessary permissions to perform the copy.

3. In SSMS, open a new query for the source database.

4. Run the following query to generate CREATE TABLE scripts for all tables in the source database:
```
SELECT 'SELECT * INTO [DatabaseDestination].[schema].[TableName] FROM [DatabaseSource].[schema].[TableName]'
FROM [DatabaseSource].sys.tables
```
Be sure to replace `[DatabaseSource]` with the name of the source database and `[DatabaseDestination]` with the name of the destination database.

5. After running the query, you will get a list of SELECT INTO scripts for each table in the source database. Copy these scripts.

6. Now connect to the destination database in SSMS.

7. Open a new query for the destination database.

8. Paste the SELECT INTO scripts copied in the previous step into the destination database query window.

9. Run SELECT INTO scripts to copy data from each table from the source database to the destination database.

Once you have run all SELECT INTO scripts, all tables with their data should be copied from the source database to the destination database.

Be sure to replace `[schema]` with the actual schema of the tables you want to copy, and `[TableName]` with the actual name of each table you want to copy.
Vishal Yelve3y ago
Hi Raju,

do refer below link

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

Also, you can restore database while taking backup of source databse.

https://www.c-sharpcorner.com/article/how-to-take-sql-server-database-backup/

https://www.c-sharpcorner.com/article/how-to-restore-sql-server-database-backup/