December 27, 2010 at 3:31 am
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
December 27, 2010 at 3:56 am
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
December 27, 2010 at 5:28 am
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
December 27, 2010 at 6:18 am
use cursor to get the list of tablename and then run the insert statement in loop by taking tablename one by one
----------
Ashish
December 28, 2010 at 6:26 am
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.
December 28, 2010 at 2:38 pm
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.
December 28, 2010 at 7:09 pm
Agreed... especially when you consider dependency order created by DRI.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2011 at 11:31 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply