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.
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.
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.
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?
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.
No one has followed this question yet.