Copy records of all Tables from one Database to another Database

Question

Post reply

Copy records of all Tables from one Database to another Database

mitramadhu06

SSC-Addicted

Points: 473
More actions
December 27, 2010 at 3:31 am

#229890

Hello,
I need to copy the records of all tables from one Database to another. Some of the tables may have different structure also. Say for example Employee table of Database1 have fields (Name, Address) whereas Employee table of Database2 have fields(Name). In this case the values of field Name needs to be copied.
I need to write a SQL Script/Stored Procedure where I could enter all the records of all the tables from Database1 to Database2. This script will be run from Installshield.
Cheers,
Madhu

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

insert into dest_tablename (col1,....coln) as

select col1.....coln from source_tablename

**** column name(1....n) from source must be matching with columnname(1.....n) destination.

----------
Ashish

mitramadhu06 SSC-Addicted Points: 473 More actions · Answer 2

Hello,

Thanks for your reply but this way I need to mention each and every table and their fields. I need a SQL Script which would loop through all the tables and all the fields without mentioning any specific table name or field name. For example I can get the table name of a database using the following Query

SELECT table_name FROM xxx.INFORMATION_SCHEMA.tables

Similarly, I need a query which would give field names and the script to copy it to another Table

In case further clarification is needed please ask for the same

Cheers,

Madhu

crazy4sql SSCoach Points: 19590 More actions · Answer 3

use cursor to get the list of tablename and then run the insert statement in loop by taking tablename one by one

----------
Ashish

bobhines Old Hand Points: 366 More actions · Answer 4

A quicker way may be to use the SQL Server Database Publishing Wizard. (See http://www.microsoft.com/downloads/en/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en) It works with SQL Server 2000 and 2005.

It will generate a SQL script to transfer the database objects and the data. If you don't need to copy/move the objects, you can cut and paste just the lines that insert the data into a SSMS query window and execute.

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 5

IMHO, to copy all data from one table to another that doesn't have the same fields or field names in a generic loop would require some pretty complex coding involving plenty of nested ifs and other checks. I think you'd be much better off just coding the migration of individual tables. It COULD be done, but I doubt it would be worth the effort.

Jeff Moden SSC Guru Points: 1003956 More actions · Answer 6

Agreed... especially when you consider dependency order created by DRI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Norsworthy SSChasing Mays Points: 636 More actions · Answer 7

I did something similar at my last job, but I don't seem to have a copy. In short, you'd have to use information_schema and/or system views extensively.

A bit of googling should net you a start on how to script out data given a table name, but to ignore missing columns, you'd have to select column details from the source table, and inner join to the destination table on column name, and optionally data type.

Foreign keys, and identity fields will certainly complicate things.