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 |
|
Taurkon
Starting Member
26 Posts |
 Posted - 2007-04-13 : 09:24:07
|
| I have 400 million rows in our of our databases and I need to move these into our Dev database for performance tuning. I don't have direct access to the server so I cannot use BCP, but the DBA has bridged the two servers for us so I can write insert statements to move data from one server to another.Can the bulk insert statement be used to move data from a target table to a destination table? Any suggestions on the best way to do this? I cannot do it in a single transaction due to performance and the amount of tempdb space required. Appreciated! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-13 : 11:00:47
|
Use OPENQUERY and figure out a way to select "the next N,000" rows. Doing TOP N,000 ... ORDER BY PrimaryKey would do, but its going to be jolly slow working out what the next N,000 records are, before the transfer even starts.Probably the best way would be to put every N,000th primary key into a table, and then use that as the basis of setting the Start and End points of each batch:IdentityID MyPK1 AARDVARK2 KRISTEN3 ZEBERDIE then you could write some sort of loop that gave you >= 'AARDVARK' AND < 'KRISTEN', then >= 'KRISTEN' AND < 'ZEBERDIE', and so on.DTS would probably do it in sensible sized batches for you, assuming that the "bridge" the DBA set up supports that. Mind you, if the DBA can make a "bridge" I don't see what you can't see a Share on the remove machine, and just copy the BCP file across the network ... plus you could Zip if first if its going to be huge.Kristen |
 |
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2007-04-13 : 12:57:29
|
| I am assuming you are the developer and the DBA is paranoid (for a good reason maybe), why don't you ask the DBA to move the data for you? Give him the statement and let him run it? If not, use what Kristen said :).May the Almighty God bless us all!www.empoweredinformationsystems.com |
|
|
|
Taurkon
Starting Member
26 Posts |
Posted - 2007-04-13 : 13:24:32
|
| Yes, I am the developer and to get dbs's to move data, or anything else, a ticket needs to be generated and then put into queue. It could take days...Of course, moving the data could take days too. Kristens solution is actually the solution I could use once data is moved over and I implement some of my changes. Currently, the table has a natural PK of 8 attributes. When I am done, it will have a identity key for a PK.DTS moves data too slowly. I was moving about 10,000 rows every 25 seconds. Anyhow, I was able to convince someone in another department with near god rights to do a BCP. His issue was finding 200GB available on a server. That has been overcome so this afternoon the copy should happen.If it does not, I have a script that copies the id's from part of the natural key into a temp table, then in a loop, inserts from source to target with a lookup in the target that retrieves TOP n rows. Once the insert is complete, I delete TOP N rows from the temp table and repeat. Should work over the weekend, but since I don't have optimal indexing, it may take a few days to run. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-13 : 14:40:08
|
| If it were me, I would just copy the entire DB, not the table. step 1. Backup db to file (Have your DBA do this)step 2. Copy backup file to dev serverstep 3. Restore the backup onto the new server (Have your DBA do this)Probably be a lot faster, plus you will have a proper dev area because your table structure will be identical to the live. |
|
|
|
|
|
|
|
|
How to move 400 million rows efficiently...