bcp utility stringing

ok. so, i've gotten the bcp utility to work and now i'd like to string all the prompts together and place them in a job to be scheduled to autorun. i don't have much experience in this arena and appreciate any help or suggestions. I'd love to be able to run (from the command prompt) a file takes care of synchronizing all my tables.

I have an additional concern. I'd like to retain the integrity of my primary keys but I notice that if i delete the information then add new, my primary key starts where it leaves off (in other words, if del. then synch. a table with 6 records, after the synch. my primary key increments starting at7). Is there a way, using the bcp util. to update records based on the primary key, copy the key verbatim, or a way to configure the table to help out with this? I know if i drop and recreate the tables as part of the job, i'm good to go but is there away around that step?

Hi DJ,

You could write all your bcp commands into a script file and then use the Windows Task Scheduler to call the bcp utility and pass in the script file. There should be informaiton on how to pass a file to bcp in Books Online, once you have it working manually, it's pretty straight forward to do the same thing using Task Scheduler.

You use of the work "job" indicates you might be familiar with SQL Agent, which is used in other Editions of SQL to schedule tasks. SQL Agent is not included in SQL Express, so that is not available to you in the Express context.

As far as your question about Primary Keys, the behavior you're seeing is by design. By definition, a Primary Key value is never repeated once used. Under some conditions there is cause to override this behavior. One way to do that is to use SET IDENTITY_INSERS ON, which is documented at http://msdn2.microsoft.com/en-us/library/ms188059(SQL.90).aspx. You can also check out the topic about keeping identity values during a bulk insert at http://msdn2.microsoft.com/en-us/library/ms186335(SQL.90).aspx.

Regards,

Mike Wachal