| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2006-09-07 : 10:18:40
|
Hi all,after quite a few weeks of investigation and trial and error I'm finally ready to start a full scale testing of my new BULK INSERT project. I have about 65 tables that needs to be bulk loaded every day and I have allready created all the scripts to do this. Do you guys have any recomandations on how I should set this up? Right now I have each script in a separate .sql-file but I can easily change this and put the scripts wherever I like. Some possible ways: - use osql? I just found this command line script that executes each .sql-file in a directory:FOR %i IN (*.sql) DO OSQL -n -U sa -P sapassword -d databasename -i %i
- use bcp (? never used bcp so I really don't know)
- create separate stored procedures and execute them in separate jobsteps
- create separate stored procedures and create a separate job for each file
- Create a DTS-package and run each script in a separate dts task
There are quite a few ways to do this but I'm sure some are better than other. I do however have two requirements:- I need to be able to log which tables gets loaded and which fails
- The loads are not dependent of eachother so even though one fails the rest should still run.
What do you guys think?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-07 : 10:40:00
|
| Depends on the file and table formats but bulk insert would be simplest.Have a look in bol and try a few to see how easy it is.==========================================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. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-09-07 : 10:47:03
|
| Maybe I wasn't clear...I have about 65 .sql-files right now all of which contain a separate BULK INSERT script for a separate file/table. All I need to do is to set up some sort of job to run the BULK INSERT on a daily basis and a way to monitor everything so I can see which (if any) has failed.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-07 : 12:24:36
|
in sql 2k5 you setup a scheduled job with 65 steps and you can set that if a step failes it goes to the next one.i don't know if that's possible in 2k versionGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-09-07 : 15:48:59
|
quote:
Originally posted by spirit1
in sql 2k5 you setup a scheduled job with 65 steps and you can set that if a step failes it goes to the next one.i don't know if that's possible in 2k version
It is possible in SQL2000.My preference would be to have a stored proc that takes filename and tablename as parameters.Yes that would mean dynamic sql.And the proc then logs success and failures.rockmoose |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-07 : 16:09:36
|
| You should get better through-put by putting them in a DTS package that runs 4 or more streams of inserts at the same time. You could also just run 4 or more jobs at the same time.Put the list of tables to be loaded into a table, and assign a job stream number to each table to be loaded. Run a master stored procedure that takes a job stream number as a parameter and executes the procedures to load each table in that job stream.You can play around with it to see how many job streams is the optimal number to run in parallel.I use this technique to load several hundred files into a database each night.CODO ERGO SUM |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-09-08 : 03:34:02
|
| I have considered spirits solution a few times but I find it a bit hard to keep track of as the overall job status would always be success even though some of the jobsteps might have failed. I could create some logging but I don't think this is the best method.I do however like Michaels idea of a master stored procedure, and especially the posibility to run several statements at once. I would keep control of everything in one place and have the same methods for logging and everything. But Michael; could you please elaborate a bit on how you got this table/procedure set up and how you have several statements run concurrently?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-08 : 07:01:16
|
quote:
Originally posted by Lumbago
I have considered spirits solution a few times but I find it a bit hard to keep track of as the overall job status would always be success even though some of the jobsteps might have failed. I could create some logging but I don't think this is the best method.I do however like Michaels idea of a master stored procedure, and especially the posibility to run several statements at once. I would keep control of everything in one place and have the same methods for logging and everything. But Michael; could you please elaborate a bit on how you got this table/procedure set up and how you have several statements run concurrently?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand"
Running statements concurrently is easy enough. Just have a DTS package with 4 TSQL steps that are setup to run at the same time.In TSQL step 1, you would have:exec P_MASTER_LOAD_CONTROL @JOB_STREAM_NUMBER = 1In TSQL step 2, you would have:exec P_MASTER_LOAD_CONTROL @JOB_STREAM_NUMBER = 2and so onProc P_MASTER_LOAD_CONTROL would select from a table a list of files to be loaded, and loop, executing the load proc for each file in the loop. The table would have the file name, load proc name, and job stream number, with one row for each file.CODO ERGO SUM |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-09-08 : 07:43:27
|
Aha...hm...pretty cool! I'll definetlty give this some serious consideration and I'm allready creating a "master database" for my job monitoring. Thanx for the all the help so far  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
Recomendations - BULK INSERT job setup