July 29, 2011 at 5:56 am
Hi,
I have a migration task where I need to merge two databases with the same name on different servers.
I can script the functions and procedures with ease but with tables it is a bit different.
I tried using export and import wizard but that just copies the data and any constraints, indexes or statistics are not copied. Is there a way to deal with this issue?
Thanks
Chandan
July 29, 2011 at 7:36 am
Not with import/export wizard.
You've a fewof options, you can script the schema in advance in managment studio by right click->tasks->Generate scripts or you can do something more complicated using SMO or SSIS.
July 29, 2011 at 7:37 am
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.
If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
July 29, 2011 at 10:59 am
Brandie Tarvin (7/29/2011)
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
Thanks for replying. I am importing data from 2000 to 2008. I can try using identity insert option when it asks me in the export import wizard, but won't it change the identity column value for some row when we start inserting it in a new table. Also, if this happens then any queries relying on identity values in joins might fail. Sorry for such a complex question, but did you understand what i am trying to ask.
July 29, 2011 at 11:39 am
chandan_jha18 (7/29/2011)
Brandie Tarvin (7/29/2011)
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
Thanks for replying. I am importing data from 2000 to 2008. I can try using identity insert option when it asks me in the export import wizard, but won't it change the identity column value for some row when we start inserting it in a new table. Also, if this happens then any queries relying on identity values in joins might fail. Sorry for such a complex question, but did you understand what i am trying to ask.
You can't create the identity column via the Wizard. The destination table has to exist and already have the identity column in it. Then you enable the Identity Insert in the Wizard if you want to keep the identity fields. If you don't want to keep the identity fields (and want the new destination to create new ones), make sure to change the mapping to ignore the source identity field and do NOT check Enable Identity Insert.
And if this didn't answer your question, then I didn't understand what part of your reply was the question...
July 29, 2011 at 12:13 pm
The easy way to do is use your visual studio or redagate schema comare and data compare
In that way you can compare and also sychrinize them on different servers but same databases
If you want more clarification follow
database-comparision-schema-compare-data-comparesynchronization-and-validation[/url]
July 29, 2011 at 12:16 pm
chandan_jha18 (7/29/2011)
Brandie Tarvin (7/29/2011)
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
Thanks for replying. I am importing data from 2000 to 2008. I can try using identity insert option when it asks me in the export import wizard, but won't it change the identity column value for some row when we start inserting it in a new table. Also, if this happens then any queries relying on identity values in joins might fail. Sorry for such a complex question, but did you understand what i am trying to ask.
I just want to make sure that i get the same identity values.possible?
August 1, 2011 at 5:33 am
chandan_jha18 (7/29/2011)
chandan_jha18 (7/29/2011)
Brandie Tarvin (7/29/2011)
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
Thanks for replying. I am importing data from 2000 to 2008. I can try using identity insert option when it asks me in the export import wizard, but won't it change the identity column value for some row when we start inserting it in a new table. Also, if this happens then any queries relying on identity values in joins might fail. Sorry for such a complex question, but did you understand what i am trying to ask.
I just want to make sure that i get the same identity values.possible?
Very possible. See the underlined statements in my quoted replies (above and below). If you don't understand what I mean by that, then let me know.
The destination table has to exist and already have the identity column in it. Then you enable the Identity Insert in the Wizard if you want to keep the identity fields.
August 1, 2011 at 8:40 am
Brandie Tarvin (8/1/2011)
chandan_jha18 (7/29/2011)
chandan_jha18 (7/29/2011)
Brandie Tarvin (7/29/2011)
The best way to do it is script the tables, indexes, etc. over to your destination server. Then use the Export Import Wizard to append the data to the existing tables.If any of your tables use Identity, make sure to enable Identity Insert. And be aware that in 2005 (don't know if it was fixed for 2008) that Identity Insert only worked one table at a time. Moving multiple tables with Identity Insert enabled tended to fail because of a bug in the system.
Thanks for replying. I am importing data from 2000 to 2008. I can try using identity insert option when it asks me in the export import wizard, but won't it change the identity column value for some row when we start inserting it in a new table. Also, if this happens then any queries relying on identity values in joins might fail. Sorry for such a complex question, but did you understand what i am trying to ask.
I just want to make sure that i get the same identity values.possible?
Very possible. See the underlined statements in my quoted replies (above and below). If you don't understand what I mean by that, then let me know.
The destination table has to exist and already have the identity column in it. Then you enable the Identity Insert in the Wizard if you want to keep the identity fields.
Thank you for replying. As I found out that export wizard does not copy anything except data, I am going to use generate script wizard to generate all table definitions including indexes and constraints.
I will create the empty tables using the script and the tables which have some identity column enabled, will be created as identity only in destination. So we have empty tables with few of them having identity columns.
Now when I go for export wizard, when i select the tables, there is an option called' Edit Mappings'. As there will be no data in the destination ,I can select 'Append data' option and as per your reply, I need to check 'Enable Identity Insert On' and the proceed.
Is this the correct way and matches the answers in your mind while you replied here:-)
The i can use 'Data compare' tool( i have trial version) to verify the data in those tables. BTW is there a possibility that, a row has the identity value at 1 and the next row as 3. If yes, then I would like the destination to be the same 1,3 and so on...
I am sure that identity resets itself but i still I want your nod.
thank you!
August 1, 2011 at 8:44 am
chandan_jha18 (8/1/2011)
Is this the correct way and matches the answers in your mind while you replied here:-)
Yes. That is the correct way.
Something to be aware of, though. If two tables (from your two different sources) use the same identity numbers and will be going into the same destination table, then there will be a conflict when you try to append the second source in your destination table. So you might want to double-check this before you start porting data over and come up with a resolution to that conflict.
BTW is there a possibility that, a row has the identity value at 1 and the next row as 3. If yes, then I would like the destination to be the same 1,3 and so on...
The data will be imported to the destination exactly as it is in the source. So if the first row has an identity of 1, the second an identity of 3, then that's how it will appear in the destination table.
August 1, 2011 at 9:12 am
Brandie Tarvin (8/1/2011)
chandan_jha18 (8/1/2011)
Is this the correct way and matches the answers in your mind while you replied here:-)Yes. That is the correct way.
Something to be aware of, though. If two tables (from your two different sources) use the same identity numbers and will be going into the same destination table, then there will be a conflict when you try to append the second source in your destination table. So you might want to double-check this before you start porting data over and come up with a resolution to that conflict.
BTW is there a possibility that, a row has the identity value at 1 and the next row as 3. If yes, then I would like the destination to be the same 1,3 and so on...
The data will be imported to the destination exactly as it is in the source. So if the first row has an identity of 1, the second an identity of 3, then that's how it will appear in the destination table.
We are not adding any table to an existing one. Tables are different and the import will be creating new tables from scratch.
BTW, have you ever seen any 2 consecutive rows having identity seed as 1(default) but instead of values 1 and 2, can it be 1 and 3? I belive identity adjusts itself in case some row in between gets deleted.
August 1, 2011 at 11:54 am
chandan_jha18 (8/1/2011)
We are not adding any table to an existing one. Tables are different and the import will be creating new tables from scratch.
My bad. I thought you were merging tables from 2 different sources. Must have been a different thread I was reading.
BTW, have you ever seen any 2 consecutive rows having identity seed as 1(default) but instead of values 1 and 2, can it be 1 and 3? I belive identity adjusts itself in case some row in between gets deleted.
Look up Identity in Books Online. It's called the Identity Increment.
August 2, 2011 at 9:13 am
BTW, have you ever seen any 2 consecutive rows having identity seed as 1(default) but instead of values 1 and 2, can it be 1 and 3? I belive identity adjusts itself in case some row in between gets deleted.
Identity values do not automatically adjust if rows are deleted. It's quite possible to have gaps in identity values when rows are deleted. That's why identity shouldn't be counted on to have consecutive numbers.
Greg
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply