Stored Procedure Recommendation Needed

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I need to send an entire datatable to SQL Server for processing. There
isn't a lot of records (maybe 100 at the most) at any given time. What
would be the best and simplest way to set this up?
My idea was to populate a temp table with the datatable and use a query
to pull the records out that I need. Just not sure of how to go about
this.
I created a sp with parameters for each field (155). This sp then
creates a temp table and runs in INSERT command with the parameters as
the values. The last step is to use the temp table in a view that pulls
out the necessary records.
Is there a way to pass the entire datatable at once rather than
executing row by row?
I just really don't want to type out 155 parameter add and value
statements.

Thanks
Mike
 
Why not to select what you need directly from your DataTable?
I don't see a reason to use SQL Server at all.
 
The data is coming in from several sites as excel files. I'm populating
a datagrid from the excel file and allowing the user to make changes as
needed (saving back to excel isn't necessary). I then need to load that
data into SQL Server. I only need unique records though. That's why I
need the sp.
 
Mike:

The way DataAdapters work is to look through the datatable row by row and
determine the RowState of each row, from there it will choose which command
to fire (UpdateCommand, InsertCommand or DeleteCommand) and then, if a
source column is specific, it will use that column's values to map to the
params.

You 'may' be able to use a Commandbuilder which could save you the hassle of
typing out all those parameter definitions but it probably won't work if
those records don't already exist, and using a commandbuilder simply to
avoid typing out param code probably isn't a good idea.

For somethign like this, I'd personally opt for DTS if possible. IF you're
writing a manual insert statement, then those values are going to have to
get there somewhere and probably the closest thing you can do to circumvent
this based on what I understand the problem to be, is possibly looping
through those values with a delimitter so perhaps you can just have a loop
run and set all of the values.

Are you using the Temp tables and ultimately the view for performance
reasons? (I'm guessing the view isn't but the temp table is). Are you
familiar with DTS? If so, and the spreadsheets are layed out the same, you
can just use one package and use different inputs - it should be pretty
straightforward.
 
I'm using the fileOpen dialog to browse to the excel file since the
files are stored in folders for the site and date received. Then
populating a datagrid. When a user saves changes they are just
accepting changes to the datatable not to the excel file. I then need
to load unique records into SQL Server since they are sending records
that we received previously along with new data. That was why I wanted
to use temp table. The temp table would be used in an INSERT statement
that pulls only unique records out. So the question is what is the best
way to populate the temp table?

Can you dynamically point to a spreadsheet with DTS?
 
Back
Top