| Author |
Topic |
|
inancgumus
Starting Member
40 Posts |
 Posted - 2004-11-08 : 07:43:28
|
| I have a 200millions rows table which will be exported to a CSV file (Unicode) row by row according to a specified filter column. The table is huge and filtering, copying, purging-the-copied-rows is a big problem.I want to move the records, not copy, because I'm gonna export at least 10millions of records then purge these records from the main table time after time so it would take so much time.How can I do this efficiently with DTS? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 07:46:03
|
| I would do it in a loop a batch at a time.bcp the data to a file then delete it.Maybe do a million recs to a file then delete 100,000 at a time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
inancgumus
Starting Member
40 Posts |
Posted - 2004-11-08 : 07:50:02
|
| Ok but how to do it with DTS? (SqlTable->BULKCOPY->CSVFile??) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 07:53:49
|
| It's easier to use bcp than DTS for something like this.When you say "will be exported to a CSV file (Unicode) row by row according to a specified filter column" do you mean you'll be exporting rows based on a condition in one column? Can you provide an example of what you mean? |
|
|
|
inancgumus
Starting Member
40 Posts |
Posted - 2004-11-08 : 08:42:03
|
Maybe but I wanna do it with DTS or I'm gonna do it over bcp-API but it may gonna be buggy...quote:
robvolk: Can you provide an example of what you mean?
Sure, it means:SELECT ... FROM HugeTableWHERE CampaignId = @campaignId |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 08:49:44
|
| Put this in a batch file and run it:bcp "select distinct campaignid from mydb..hugetable" queryout c:\campaigns.txt -Sserver -T -cfor /F %%a in (c:\campaigns.txt) do bcp "select * from mydb..hugetable where campaignid='%%a'" queryout c:\campaign%%a.txt -Sserver -T -cThis will get all of the campaign codes into a text file. The for command will enumerate through that file and run bcp for each campaign code. Each set of data for an ID will be put into a file named "campaign[ID].txt" where [ID] is that actual ID.You can, of course, create a campaigns.txt file any way you like, containing one or only a few IDs, it's up to you. As long as there is only one ID per line. You may have to adjust the 2nd query statement, I don't know if campaign IDs are numeric or character (remove the single quotes if they are numeric)If you want to run this from a regular command line and not a batch file, change all the %%a to %a. |
|
|
|
inancgumus
Starting Member
40 Posts |
Posted - 2004-11-08 : 09:01:44
|
| Thank you but I already did this, I was searching for doing it over DTS. Maybe I have to write my own bulk copy archiving task to DTS.Thanks for your precious time. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 09:12:12
|
| OK, why DTS when this does what you want? Or does this not do what you want? |
|
|
|
inancgumus
Starting Member
40 Posts |
Posted - 2004-11-08 : 09:17:11
|
| Because I have designed a user interface which shows process status of the export/import in percentage, then users become aware of what is going on. This is a requirement. In DTS or using bcp-api I can do this but bcp utility I can't. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 10:18:57
|
| Well you could loop doing 5% per time and use that as the counter.Or monitor the size of the output file.Or write the output to another file and monitor the size of that or keep opening it and count the number of recs==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
inancgumus
Starting Member
40 Posts |
Posted - 2004-11-08 : 10:23:36
|
| Thanks all but Im gonna use odbc bcp-api with c++ for flexibility and greater managebility... |
|
|
|
|
Bulk Export