csv import which runs stored procedure

Are there any tools which allow you to take a comma delimted file and run a stored procedure for each line in the file, passing the values in the file as parameters? i.e.

CREATE PROCEDURE add_user
@.name varchar(20),
@.first_name varchar(20),
@.last_name varchar(20)
AS
/* do some lookups */
/* validate input */
insert into users (name, first_name, last_name) values (@.name, @.first_name, @.last_name)

Then a comma delimited file such as
bpeikes,Benjamin,Peikes
rbobly,Robert,Bobly
trichard,Tom,Richards

I need a tool which can execute the stored procedure once for each line in the file. I could write one myself, but I'd like to know if any of the tools which come with SQL Server will handle it.

My preferred method would be to first load the csv file data into a 'staging' table, and then have a procedure that validates, cleanses, massages, and transfers the data to the working table(s).

And that can easily be accomplished using a SQL Agent Job, scheduled to run 'on schedule', or available to run 'as needed'.

|||

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

|||

You could add a trigger to your target table that calls your stored procedure, bcp the data into the target table and it should trigger storedproc calls for each insert.

Hope that helps,

John

|||

bpeikes wrote:

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

Maybe you should use the sql server integration services SSIS

instead