Passing a Table to A Stored Procedure

Question

Passing a Table to A Stored Procedure

Viewing 15 posts - 61 through 75 (of 100 total)

You must be logged in to reply to this topic. Login to reply

Love2Learn SSC Rookie Points: 28 More actions · Answer 1

Nice Sebastian,

Here's my question for you? How do we update regularly an oracle table (Destination)from our SQL table (Source) thro' DTS Package in SQL Server 2000???

Thanks

Vasudev

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

Hi

I do not think it is a good idea to run a DTS package continuously. Probably you should look for REPLICATION options. I never got a chance to work much with oracle. I dont know if ORACLE can subscribe to a SQL Server 2000 publication. If it supports, replication is the best option.

I leave this question for the EXPERTS in this forum to answer 🙂

.

jrbirdman Valued Member Points: 64 More actions · Answer 3

Scott Gammans (5/30/2007)
I don't think Jacob is the one suffering from a lack of knowledge, Sergiy. What happens when two different users simultaneously call the same stored procedure? Kaboom, that's what happens. And if you think that scenario is unlikely, you haven't worked on OLTP systems.

Scott,

Actually, I think it would work. Each execution of the trigger is within the context of a connection's session and therefore isolated from any other users or connections. How else could a trigger work in the real world?

The problem I have with Sergiy's solution is that, while it's a neat trick, it's simply that...a trick. I would rather design something that, abeit slower and maybe not as slick, 90% of dba's could look at an instantly say "I get it" rather than staring at it for 15 minutes, scratching their heads, and muttering "WTF". It's using a technique for something that it wasn't originally designed for.

That said, it's great to see someone "think around corners".

Dave

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

A trigger based system could work, as could an asynchronous mechanism other than replication as well.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sergiotorres SSC Journeyman Points: 79 More actions · Answer 5

Very helpful, Jacob. Thanks.

I have a little different problem:

I have some records in a datatable that the user has created using a gridview in a webform.

I would like to pass them all together to a stored procedure that is going to calculate some values and then, insert all records in a table.

Is it possible to pass the datatable as an xml variable? I tried datatable.writexml, but couldn't make it...

Thanks for your help,

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

sergiotorres (12/8/2007)
Very helpful, Jacob. Thanks.
Is it possible to pass the datatable as an xml variable? I tried datatable.writexml, but couldn't make it...

I think there should be no problem doing this. It would involve two tasks. 1. retrieving an XML representation of the data stored in the data table. 2. passing the data to SQL Server. You said you could not make it, but did not mention where the problem was.

.

sergiotorres SSC Journeyman Points: 79 More actions · Answer 7

I could not find a way to get the xml representation of the datatable into a variable (my thought was to assign that variable to an ado.net command parameter).

I have never used xml, this said, I tried something like this:

dim myXML as xml

myXML = mydatatable.writexml()

I tried several of the signatures of writexml and always got a message saying my expression "mydatatable.writexml" does not return a value...

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

WriteXml() does not work that way. It does not return a string. You need to create a stream object first and pass it to the method. The content of the xml is written to the stream object you are passing. Then you need to extract the XML data from the stream object.

.

jacob sebastian SSChampion Points: 11822 More actions · Answer 9

sergiotorres (12/8/2007)
I could not find a way to get the xml representation of the datatable into a variable (my thought was to assign that variable to an ado.net command parameter).

I have created a sample application and has posted it at http://jacobsebastian.blogspot.com/2007/12/passing-data-table-to-sql-server-2005.html

.

sergiotorres SSC Journeyman Points: 79 More actions · Answer 10

Thank you very much, Jacob!

Your sample app was more than I could hope.

Thank you again,

Manish Sinha Ten Centuries Points: 1023 More actions · Answer 11

I am having a dot net application.Suppose I have to insert 5 rows in particular table.wat i do is that I call the sp once for each row.Which is better approach performance wise if I do it in this manner or if pass all the 5 records as a table.

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

A bulk insert method will beat individual inserts in most if not all cases. Here you would avoid the network and execution hits in addition. Linchi Shea did a good blog post on this: Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jacob sebastian SSChampion Points: 11822 More actions · Answer 13

Manish Sinha (12/10/2007)
I am having a dot net application.Suppose I have to insert 5 rows in particular table.wat i do is that I call the sp once for each row.Which is better approach performance wise if I do it in this manner or if pass all the 5 records as a table.
Thanks

The best way is to pass all the records in a single XML buffer and do a single INSERT statement to save all the data to the table at once. Create a stored procedure which takes an XML variable. This XML variable should contain all the records that you need to insert. The stored procedure should use XQuery to SELECT data from XML variable and INSERT into the table.

.

yeluri.anitha SSC Enthusiast Points: 132 More actions · Answer 14

yeluri.anitha

SSC Enthusiast

Points: 132

December 10, 2007 at 6:59 am

#758569

inert data into table is stored procedure

the sqlist SSCrazy Points: 2398 More actions · Answer 15

jacob sebastian (12/10/2007)
Manish Sinha (12/10/2007)
I am having a dot net application.Suppose I have to insert 5 rows in particular table.wat i do is that I call the sp once for each row.Which is better approach performance wise if I do it in this manner or if pass all the 5 records as a table.
Thanks
The best way is to pass all the records in a single XML buffer and do a single INSERT statement to save all the data to the table at once. Create a stored procedure which takes an XML variable. This XML variable should contain all the records that you need to insert. The stored procedure should use XQuery to SELECT data from XML variable and INSERT into the table.

I think the question first should be in what format do you have those 5 rows? Where do they come from and then decide if the XML approach is "the best", which personally I highly doubt.

Maybe we shold set some things strait here, SQL server as any relational database platform was design to work in the best, optimal way with datasets and it was built to deal with this kind of construct. SQL server 2005 introduced the new XML data type and all the goodies around it only as an extra feature to be used in special situations when a problem requires it. Having said that, from here to replace the dbms relational native toolds with XML is not good practice and profound against what all the relationa database ideea is.

In conclusion don't jump for an ideea and just use it as a panacea solution only because "looks cool", this is not Hollywood we're talking here.

Regards.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist