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 |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-03-31 : 05:39:21
|
| I have a set of data that I need to copy back into the same set of tables repeatedly. I use the same data, although I have to change some of the values. I originally was selecting the data out to a file and performing the changes at that point (using BCP and a select statement to queryout to a file). However, the export was taking longer and longer becuase of the size of the data. Therefore, I decided to opt for a different approach where I would export the data out the once, and then import it over and over again, but making the changes to the data as part of the import.I have the following query which doesn't work (executed as part of a batch file):bcp test.dbo.test1 in "SELECT TranID+!MaxTranID!, CASE StoreNo WHEN 10 THEN !StoreNo1! WHEN 11 THEN !StoreNo2! WHEN 15 THEN !StoreNo3! END FROM OPENROWSET(BULK D:\CreateTestData\dbo.test.dat', FORMATFILE = 'D:\CreateTestData\dbo.test.fmt')AS a" -SWS23 -T -c -EThe reason it doesn't work is because of the OPENROWSET SQL Statement. But you can do a queryout to obtain the results from a file, so why not query in???Is there a way to manipulate the data as part of the import?Hearty head pats |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-31 : 05:45:40
|
No. OPENROWSET is a T-SQL command and BCP is a Windows command file.Maybe you should restate your current situation and explain to us what you have to do.What sources of data do you have? What should happen with this data?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-31 : 06:32:26
|
| Hi PesoI have a database, which has 2 weeks worth of data (about (although this may differ from client to client). The current data set consists of a number of customer transactions (at a retail store) for 4 stores. I need to increase this data set to up to 2000 stores. So essentially, copy it x amount of times (2000 is a configurable value).Each transaction has a TransactionID (BIGINT) which is unique across stores. To make matters a little more complex, we have split the tables into a separate set of tables for each day:Original set of tables:HeaderItemTenderTables split into daily partitions:Header_20090101Item_20090101Tender_20090101Header_20090102Item_20090102Tender_20090102........ etcI have tried a number of solutions to this problem:SSIS (this was abandoned as we cannot rely on SSIS being installed on the client side)SQL Query (selects the data out from the tables, performs manipulation and inserts back in (so use dynamic sql to do all the tables) ... this runs iteratively. This was abandoned as this also kept failing when running in the night with a Transport level error?. Also was very heavy duty on the SQL server)SOOOoooo, I am now trying to use BCP:I have a procedure that creates the BAT file (so obtains the maxTranID, the storeNos, etc).First effort: Data is bcp'd out using a queryout select statement, and performs the required changes. This is then copied directly back into the tables from the file. Issue with this method is that each time the data is selected out, the size has doubled and the time taken to bcp out the data was getting longer and longer).Second effort: data is bcp'd out. Procedure creates a series of BULK INSERT statements to be executed on the server. Issue with this method is that it would fail (run out of memory) if there are too many bulk insert statments executed at a time (I'm talking a few thousand here)Current method: BCP the data out the once, and iteratively insert back in (hoping to use bcp) the required number of times. I thought this would be the best approach, as the data is selected out the one time, and then I can perform the majority of calculations in the procedure to create the bcp statements.Other issues include:The method has to be repeatable (so easy to pass over to clients when they wish to increase their data for testing)The method has to not interfere 9as much as can be possible) with testers who are still working on the same serverThe method has to be as quick as possible (although the sheer volume means it will be running for a long long time)The method has to be able to be re-run if it falls over (though at the moment this is not as important as all the above)Any help would be greatly appreciated. I really should have finished by now, but everything I try seems to come up with an unresolvable issue... ARGHHHHHHHHHH!I know you will want volumes, but I cannot provide accurate ones for you... I would need to run a query but I am still running BULK INSERT statements against the DB (only been doing this for 4 days now).I do know we are talking millions (eventually will be about 7million per day for 2000 stores, so that equals 98,000,000 (and the item tables has about 15 records per item, so that is 98 million x15).Oh, and the second part of the task is to copy the 2 weeks worth of data (once I hvae 2000 stores), and increase to 6 weeks worth!!! This all has to be completed by next week!Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-31 : 06:56:46
|
| perhaps i can use sqlcmd to do the bulk insert statment?Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-31 : 07:54:57
|
| Attempt 2563.............Using SQLCMD:sqlcmd -SWS23 -dtest -E -Q "INSERT INTO test.dbo.test1 SELECT TranID+!MaxTranID!, CASE StoreNo WHEN 10 THEN !StoreNo1! WHEN 11 THEN !StoreNo2! WHEN 15 THEN !StoreNo3! END FROM OPENROWSET(BULK 'D:\CreateTestData\dbo.test.dat', FORMATFILE = 'D:\CreateTestData\dbo.test.fmt')AS a"I'll be back with this issue I am sure.............Hearty head pats |
|
|
|
|
|
|
|
|
Using BCP and BULK INSERT statement