Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2010-04-07 : 04:27:55
|
| I have a large table in a sql 2005 database which needs copying over to a 2008 database.This table contains an identity field (recno) and when I copy it over I will need to partition the new table in the 2008 datbase to make accessing it faster. (the 2005 table is NOT partitioned).I have created a Partition function and a partition scheme in the 2008 database ready for the transfer, but have not yet created a table to recieve the data as i am uncetain about handling the identity field.Whats the best way forward on this, as it is the first of about twelve tables that need copying over in this fashion, all others also have identity fields which need to be kept as they are (the recno field links across to some other tables so it is needed). |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2010-04-07 : 04:31:38
|
| Oh, and I forgot to mention, when I partition the table, the partition will be based on one of the date fields, NOT the identity field. |
 |
|
|
Asken
Starting Member
38 Posts |
Posted - 2010-04-07 : 04:31:38
|
| If it's a onetime thing just disable the identity on the 2008 tables, copy and enable it again.Still applies after you second post... |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2010-04-07 : 04:37:57
|
| Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-07 : 04:41:52
|
| To preserve the existing Identity value use INSERT_IDENTITY ON (on the remote table) during the transfer - and don;t forget to turn it OFF afterwards!Disabling and reenabling will require a complete copy of the table (to temp table, and rename back again) including dropping / recreating all FKeys etc, won't it?If so, and given that this is a partitioned table, and I assume therefore "large", I reckon that is quite a lot of effort, CPU-time, TLog disk space etc. |
|
|
|
Asken
Starting Member
38 Posts |
Posted - 2010-04-07 : 04:54:39
|
| Kristen, you're right on the identity column!However on the foreign keys and stuff it should be added after the move with check to get some speed out of the move. The same applies to indexes. If created they should be disabled and rebuilt when the data has been loaded or added afterwards.I'm assuming that the data is already in good health when the move takes place...Reporting & Analysis Specialist |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2010-04-07 : 05:23:52
|
| Thanks both for your help, 80 million rows (10GB) data transferred for the first table. |
|
|
|
Asken
Starting Member
38 Posts |
Posted - 2010-04-07 : 06:42:42
|
I too want the enterprise edition in production!!! 
Reporting & Analysis Specialist |
|
|
|
|
|
|
|
|
Question on copying tables from 2005 to 2008