We help IT Professionals succeed at work.

hourly Bulk Insert w/ WHERE clause (or workaround)??

bt429 asked
Last Modified: 2011-09-20
Hi all,

I need to bulk insert from a web .log file to a SQL Server 2000 database on a recurring basis.  I have the bulk insert working for a daily insert using a Windows scheduled task, but wish to update this to hourly.  I see (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) that the WHERE clause is not accessible with Bulk Inserting.  

Can I filter this at all so that entries in the log file do not get inserted multiple times into my database?

 I would like to reduce the redundancy in the db.  I think that it would likely be best for me to either track the newest ID of the row (can I add a unique ID for a table which is based on using preplog.exe?) and delete new entries more than an hour old, or to create a new log file for every hour (my gut feeling is that this is not advisable).  This is my first post, so I hope 300 pts is acceptable.

Many thanks.
Watch Question

you have the firstrow and lastrow options...but you have to figure out if they are usefull in your case or not...

good luck,
This one is on us!
(Get your first solution completely free - no credit card required)

yap..the neatest solution would e if you would receive a file every hour, and from that file to bcp the records...but this is only if you are able to receive that file on hourly bases...



Thank you both for the help so far.  I do have a few questions:

I'm not sure how to create the hourly log files, other than renaming the original log file and then re-creating a blank text file, with the same name as the original log file.  Is this how I would do that?  I suppose in this case if I ever did want all 24 hours then I could just concatenate all 24 files into one.  Do you know if I will have to re-create the blank file (named ex020704.log) if I rename the file every hour (to ex020704_09.log or ex020704_21.log, for example) or is this something IIS will automatically do on the next log file entry.

My log files do have date information in them, so I suppose I could iterate through the log, create a string of that information and then create a text file from that string, but I think the new file is much easier for now.  

Can you elaborate on this? - BTW, tt's far easier to concatenate lots of 1-hr pieces together after you've improted then than to extract 1-hr pieces form a bigger file.

I'm not sure what "bcp the records" means.  Can you explain the acryonym?

Thanks again,
Top Expert 2004
This one is on us!
(Get your first solution completely free - no credit card required)
thx arbert. my w3 svc wouldn't start so I couldn't check it.
Top Expert 2004

yep, I agree with monos statements above as well, the smaller logfiles are easier to work with than "one" larger logfile :)

Look in BOL for bcp..

Here is what books online (BOL) says:
The bcp utility copies data between an instance of Microsoft® SQL Server™ 2000 and a data file in a user-specified format.

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]


Yes, arbert, I am using IIS so I will investigate this on Monday.  I will then just have to update my scheduled task (a simple .vbs file) to accept the file name with the hour appended.  I think the bcp option would work as well but it seems I am already well on my way to the alternative.

Thanks again to all.  Hopefully this will just be a matter of updating my IIS settings, and then splitting the points appropriately...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.