For eg. We have Dept Master for which user selects Department name and based on selection the Grid which has Employee records is displayed in grid along with check boxes.
User selects some checkboxes and we want to insert the rows which are selected(checked ) in GridView.
We want to implement this as one transactions that means we donot want single record to be inserted at one time using loop in the gridview, all selected records should be inserted in one go.
We do not want to create table TYPE (because we have the similar scinario for many tables)
You can implement a common stored procedure that takes an xml string. XML-parsing in tsql is really easy now days and I am sure you can come up with a generic format
for all your tables.
In my team we have used custom table types so many times we now have tools that creates the types for us. I prefer sending a DataTable than an xml-string to a stored
procedure.
ZeeshanAnsar...
Participant
878 Points
264 Posts
Multiple rows insert in SQL Azure
Jun 20, 2012 10:55 AM|LINK
We need to insert data in SQL Azure.
We have master -details scenario.
For eg. We have Dept Master for which user selects Department name and based on selection the Grid which has Employee records is displayed in grid along with check boxes.
User selects some checkboxes and we want to insert the rows which are selected(checked ) in GridView.
We want to implement this as one transactions that means we donot want single record to be inserted at one time using loop in the gridview, all selected records should be inserted in one go.
We do not want to create table TYPE (because we have the similar scinario for many tables)
Any suggestion???
Please 'Mark as Answer' if this post helps you.
rickevry
Member
386 Points
88 Posts
Re: Multiple rows insert in SQL Azure
Jul 06, 2012 07:31 PM|LINK
You can implement a common stored procedure that takes an xml string. XML-parsing in tsql is really easy now days and I am sure you can come up with a generic format for all your tables.
In my team we have used custom table types so many times we now have tools that creates the types for us. I prefer sending a DataTable than an xml-string to a stored procedure.
ozkary
Contributor
2034 Points
303 Posts
Re: Multiple rows insert in SQL Azure
Jul 11, 2012 02:35 AM|LINK
The best way to insert multiple records in one transaction is to use select into with a combination of union statements.
http://ozkary.blogspot.com/2012/02/insert-multiple-records-in-one.html
you may need to figure out a way to get the data into the multiple rows (i.e. temp table, xml)
hope it helps
og-bit.com
mandarj123
Member
708 Points
291 Posts
Re: Multiple rows insert in SQL Azure
Jul 19, 2012 09:19 AM|LINK
you can do that using table variables feature in sql 2008.provided you are not using entity framework.
because entity framework does not support table variables.
Capgemini India.