create batch file and update stored procedure

[mbfloyd]

Hello, I am trying to create a batch file that retrives a text file (comma delimited)thats created daily and runs an update stored procedure that updates and inserts new records. Such as new employees or updates employees data. Key is employee number and/or table id number. Please help
Thanks

Mack

 

[sguslan]

I would create an SQL DTS package to read the text file into a table the on the same package run a stored procedure or an sql command to insert/update data into the table to be updated. The DTS is a graphical interface wizards that help you in such tasks. You can create one from the sql Enterprise manager's Data Transformation.

The basic steps:
1 - create a new DTS package
2 - Create a table in sql with proper column definitions
3 - Create 2 connections, one for the source text file and the other for sql server
4 - Create a data transformation link between the 2 connections with text file being the source
5 - Complete the data trasformation design wizards
6 - Create an SQL command task insert in it 2 sql command; 1 to delete any records in the temp table and 2 to insert and/or update the target table
7 - schedule the task by saving the DTS package then right clicking the package icon and selecting the context menu to "schedule the package"

 

[tlbroadbent]

I agree with sguslan that DTS is a great solution that should meet your need.

If you want to use a batch file and execute command line programs, consider using OSQL to start the SP and use the BULK INSERT command in the SP to import the text file. Alternately, you can use BCP to import the file to SQL Server and OSQL to execute the stored pocedure. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.