How to copy all tables from one database to another in SQL Server?

Question

I have created a database in SQL Server named "devdatabase" and another named "pocdatabase". Both of these databases have the same tables but only devdatabase has data populated in the tables. What is the best way to populate pocdatabase with the same data in the devdatabase tables?

Best way to move databases is to back up a database and restore it on another server. If you specifically want to copy, then there are a few choices: 1. As per @Teodor Nitica, you can write INSERT INTO .... SELECT queries (Excel can help with generating these queries) 2. Use a data compare tool such as "Redgate SQL Data Compare". There are lots of tools available, some free, some not so free. — Alex
– Alex, Commented Jun 15, 2022 at 3:23

Teodor Nitica · Accepted Answer · 2022-06-15 03:26:09Z

1

It depends on how many tables there are and only if you want the data in the tables or to copy other objects. If there are not hundreds of tables I would use append queries.

INSERT INTO DestinationDB.dbo.tableName SELECT * FROM SourceDB.dbo.SourceTable
INSERT INTO pocdatabase.dbo.tableName SELECT * FROM devdatabase.dbo.tableName

DestinationDB="pocdatabase" SourceDB="devdatabase" I assumed that the table names are identical. If you have a lot of tables so the above method is tedious then you could backup the "devdatabase" then restore the backup in "pocdatabase".

edited Jun 15, 2022 at 3:26

answered Jun 15, 2022 at 3:17

Teodor Nitica

211 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2022-06-15 02:34:43Z

-1

Using SQL replication or data compare with other tools. ex: Redgate SQL Data Compare.

edited Jun 15, 2022 at 2:34

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Jun 15, 2022 at 2:29

Yune

151 gold badge1 silver badge6 bronze badges

2 Comments

Dale K Over a year ago

Answers need to provide a complete solution to the question asked, not just tips and resources.

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to copy all tables from one database to another in SQL Server?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related