I have two databases with THE SAME schema. I need to copy the contents of all the tables from one database into the other database. What is the best way to do this>? There are about 200 tables. I have ssms.
views:
59answers:
5
+2
A:
Get yourself Red-Gate SQL Data Compare - best tool for the job, hands down.
Update: if you can't or don't want to spend any money, but instead want to spend a lot of your time, you can of course do something like this for each table:
INSERT INTO TargetDatabase.dbo.YourTable1(list of fields)
SELECT (list of fields) FROM SourceDatabase.dbo.YourTable1
and then repeat this for the other 199 tables, too.
marc_s
2010-10-26 21:18:57
@marc: i just want to copy everything from one database into the other
i am a girl
2010-10-26 21:20:07
yes - get SQL Data Compare for that job - it's the best tool around to do this.
marc_s
2010-10-26 21:22:16
@marc is there a free way to do this?
i am a girl
2010-10-26 21:22:49
No - either you spend a lot of your time (is that worth nothing?), or you get a tool to help you
marc_s
2010-10-26 21:24:06
@marc u are definitely right, but i would need to tell my boss that he has to pay $400 to copy some tables
i am a girl
2010-10-26 21:26:20
How many hours will you need to spend to manually copy 200 tables??? 10 table per hour = 20 hours..... how much do you earn per hour ?? Yes, it costs money - once, when you buy it. You'll **save** tons of your precious time over the months of using it .....
marc_s
2010-10-26 21:27:04
@marc it will take me maybe 30 min, thanks to you helping me getting all the tables names , i can just do what you suggest excel (create the 200 insert statements quickly and im done) i already have a list of the tables: use whd;goSELECT [Name] = o.name , [RowCount] = SUM(p.row_count)FROM SYS.DM_DB_PARTITION_STATS pINNER JOIN SYS.TABLES o ON p.[object_ID] = o.[object_id]WHERE index_id <= 1 -- Heap or clustered index onlyGROUP BY o.nameORDER BY 2 desc
i am a girl
2010-10-26 21:28:46
+2
A:
I use RedGate's Data compare it works really well comes in handy when trying to solve data issues.
AGoodDisplayName
2010-10-26 21:19:43
@agooddis i just want to copy everything from one database into the other
i am a girl
2010-10-26 21:21:12
+2
A:
If you don't care about the data in the 2nd database (you're not looking for a merge), you can back up your database, then restore it over the other one.
I can post screenshots to demonstrate how if you like.
~~~~~~~~Screenshots added~~~~~~~~~~~~~~
Open management studio & connect, then go into backup database from Tasks menu:
Then click OK button to back up database (Note, picking a different back up directory such as C:\ may not be a bad idea so that it is not "in use" when trying to restore it later):
Select Restore for the 2nd database you wish to paste over:
Select the 1st database from the list, or browse to the file to backup from:
It may be neccessary to check this check box on the options tab when performing a restore over existing data:
Click OK and it should work.
Jrud
2010-10-26 21:26:22
i just want all the data frmo one db to be copied to the other one. i would LOVE some snapshots! thanks
i am a girl
2010-10-26 21:31:35
+2
A:
You can use the export wizard in SSMS. Right-click on the source database, select Tasks/Export data and follow the steps. A little tedious for 200 tables, but it's free.
Mike K.
2010-10-26 21:27:31
+1
A:
Without spending $, you could use the SELECT ... INTO ... syntax -- providing the table specified in the INTO clause does not exist already in the target database:
SELECT *
INTO new_db.dbo.table
FROM old_db.dbo.table
But that won't migrate constraints, triggers, jobs, logins, roles, etc. If the databases are on different hosts, you can use a Linked Server instance to connect them and use four name notation to reference the remote instance.
As for dealing with ~200 tables, you'd need to use dynamic SQL to create the statement because you can't supply a table name as a variable in dynamic SQL. The list of tables can come from either SYS.TABLES or INFORMATION_SCHEMA.TABLES
OMG Ponies
2010-10-26 21:29:18