Hi, I need to pass a table of 4000 records as input parameters to a stored procedure that inserts the records into other tables. Each of the SP parameters is a column in the input table. How do I do this in SQL 2005?
Thanks for any help here.
asked
Passing input parameters to Stored Procedure from table
If this is a one-time data load and you want to do this in sql, then why not create a select statement to create an execute statement for each record to be inserted? Something like:
SELECT 'exec spname ' + CHAR(39) + col1 + CHAR(39) + ', ' ... (repeated for each parameter) FROM SourceTable
You could send the output to a text file, then run the file from SSMS. It's not elegant, but should work for a one-time process.
BTW, I find using CHAR(39) easier when you want to wrap some part of your results in single tick marks.
I wouldn't recommend this approach at all. It means you're calling a statement 4000 times. Instead I would strongly suggest you either use INSERT...SELECT to move the data from one table into another or that you look at setting up SSIS as a mechanism for extracting and loading the data.
If the rows are in a table then why do you need to pass them as parameters? You could write a new version of the proc that just inserted direct from the source table. Or you could change the proc to accept a table-valued parameter and insert from the TVP.
This is a very good approach. There was a question few days ago about how to pass the data table from C# into the stored proc, and I typed the complete sample detailing how to do it here:
http://ask.sqlservercentral.com/questions/7307/how-to-update-the-session-datatable-from-c-net-to-sqlserver-2008/7309#7309 If the TVP approach is an option then glancing through the sample might be helpful in order to sort out details.
But since this is a 2005 instance the TVP approach won't work. It is a better idea than the on picked as an answer though.
@Grant - yep, this is exactly what too much coffee does, it prevents one from thinking straight (no TVP in 2005, doh). There is another approach consisting of selecting source as xml and then passing that xml to the proc in question as a parameter. Inside of it it is easy enough to insert/update d from destination table d joined with xml. I have to use this approach for moving data between the servers which do not have connection between them, and interestingly, the db engine is capable of processing 100s MBs of XML in 1 set in a surprisingly short time even though no editor can open such xml
Thank you for your answers. I do not have control over the sp, but i do know that it inserts into several tables etc.
This is a one off data takeon. I have been told by sp developer to use the following:
Exec spname param1,param2 and so on ( there are 22 of them)
I know the parameter names. Is there some way I can loop through a recordset passing values one row at a time?
Sure, with a cursor. But the problem is still passsing them all in. One time load or not, this is a silly approach.
From a .NET program, you can get the data from your table into a DataTable object, and then iterate the columns for each row in that object.
This VB.NET code won't work (i'm on a surf-only box), but might provide a starting point for you:
Dim cmd as new SqlClient.SqlCommand("SELECT * FROM someTable",<some connection object>)
Dim da as new SqlClient.SqlDataAdapter(cmd)
Dim dt as new DataTable()
da.Fill(dt)
For Each r as DataRow in dt.Rows
dim cmd2 as new SqlClient.SqlCommand("sp",<some connection object>)
cmd2.CommandType=<constant value for stored proc, auto completion in VS.NET will guide you>
For Each c as Column in dt.Columns
cmd2.AddWithValue(c.Name,r.Item(c.Name))
Next
cmd2.ExecuteNonQuery()
Next
Hope that works as a starting point.
Magnus, This is pretty unsafe practice you introduce in your answer :( Both commands and adapters are disposable, and your sample includes cmd2 scoped inside of the loop, i.e. it is allocated, populated with params and their values and then executed 4,000 times (according to the question). Well, the trouble is that every one of these 4,000 instances references the connection, which is still alive after the last iteration of the loop. This can lead to severe bloat of the heap. At least, cmd2 should be scoped out with parameters and only setting values and execute inside of the loop.
Oleg: You're right, the code I provided is not the best, perhaps not even good. And yes, cmd2 should be moved outside the loop, and the parameters collection should be created before going into the outer loop as well. That's probably how I would have created it, would it have been a live scenario. To my own defence - I did state that the code won't even work as provided. It serves as a starting point.
Magnus, I saw the statement about the starting point, and I am sure that you would opt for the optimized version you described. I just know that unfortunately, it happens more often than not that some snippets are just taken and used as is despite the starting point warning. For example, many of the MSDN examples do not bother to include the commands into the using blocks simply because these examples are intended not for prod use but as starting/reference points. This occasionally leads to someone taking the snippets as is and then introducing hard to debug leaks into the prod apps.
