April 30, 2008 at 2:30 am
Hello,
i´ve a rather long sql - script which moves data from one database to an other. This is done via a couple of "insert into select * ....." scripts. We can´t do a drop table, select * into because not all of the data is copied, and we can´t loose the data already in the destination table.
So far the script works perfectly, but as soon as the source table changes the script will fail, because the number of columns is not equal.
Doe´s anybody know how we can handle this issue ? I´ve already tried to get the error - message, but non the less the batch - job will terminate as soon as the 2 tables are not identical...
Maybe someone can tell me a short statement how i can check if the two tables have the same number of columns ??
Thanks for your help !
rgds
Klaus
April 30, 2008 at 2:35 am
Hi
How about specifying column names in the insert as well as select clause
ex:- insert into table1 (col1,col2)
select col1,col2 from table2.
"Keep Trying"
April 30, 2008 at 2:39 am
Hi,
the problem is, that the script is generated automatically, and there are about 400 different tables which should be "copied" ...
The detailed specification of the different columns in each of these statement would be rather difficult to do ....
rgds
Klaus
April 30, 2008 at 2:51 am
[font="Verdana"]Then why don't you go for Replication?[/font]
MH-09-AM-8694
April 30, 2008 at 2:58 am
Replication isn´t possible, because not all data will be copied, there is a central table with all the changes in the databases (based on triggers), and only the Inserted data in the database should be processed .....
April 30, 2008 at 3:05 am
[font="Verdana"]Well in that case you need to go the way Chirag has shown.
Mahesh[/font]
MH-09-AM-8694
April 30, 2008 at 5:16 am
Does column addition/deletion/modification happen regularly?
The script needs to be changed to accomodate the column changes . how is it done? How difficult is it to make any changes in the tool.
You can write a small query to check whthr the columns of the same table are different in the 2 database.
"Keep Trying"
April 30, 2008 at 5:20 am
Hi,
in fact changes occur from time to time. My idea behind was that as soon as the insert "fails", i just drop the destination table (because it´s invalid anyway), and do a "select * into ...." ....
May 1, 2008 at 11:15 pm
Hi
Keep in mind that select * into does not create indexes, Primary keys,Foreign keys and default values for column.
It would be much better if you apply the source table changes to the destination tables and then move your data. There wont be any error and this is the way its normally done.
You said that you need to keep the destination data. If you do select * into you will have only the source data. Is this what you want ?
"Keep Trying"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply