How to update multiple rows in single update query.

Question

i want to Improve performance by using single update statement. already i hit multiple times for update data in table i want to avoid this. i want to use like BCP Command. Note - Using VB.net 2.0 Code

Ramesh Maruthi · Answer

bcp databaseName.Schema.TableName *or* Query in out *or* queryout -S ServerName\instanceName -U userName -P password *or* -T -c *or* -n *or* specify storage information for each column bcp UPDATE Customer SET Customer.cust_no = p.cust_no Customer.cust_name = p.cust_name FROM Customer INNER JOIN @Customer_input p ON Customer.id = p.id queryout C:\bcp_outputQuery.txt -SYourServerName -T -c

Abhijeet Chavan · Answer

thanks...but i dont use any SP ..Can i go with BCP Command ? How?

Jignesh Trivedi · Answer

Hi Create stored procedure that accept either table value parameter or xml as input. here table value parameter or xml is nothing but data that you want to update in to database that contain primary key data... write update statement with Select Query like UPDATE Customer SET Customer.cust_no = p.cust_no Customer.cust_name = p.cust_name FROM Customer INNER JOIN @Customer_input p ON Customer.id = p.id Refer http://www.c-sharpcorner.com/UploadFile/ff2f08/table-value-parameter-use-with-C-Sharp/ hope this will help you.

Abhijeet Chavan · Answer

cust_no cust_id cust_name cust_addr 10 20 abc xyz 11 21 abc1 xyz1 12 22 abc2 xyz2 i want to update cus_name and addr on basis of cust_no and cust_id in only one database operation. must update 3 records at a time. .. no i don't want delete existing data

Jignesh Trivedi · Answer

hi Abhijeet Please provide more information like which type of data you want update in bulk... also provide some test data. In this case you can do one thing delete existing data and insert new data