Click here to Skip to main content
14,933,981 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
i tried all insert, delete and upadte queries as single rows affected

i have tried insert MULTIROW insertion,deletion and updation i get confused, most of the refernces are give as like

Insert()
Insert()
Insert() for same procedure for delete

delete()
delete()
delete()

operations.this multi row insertion and deletion used for store procedure and trigger creation

so i need clear explaination about multi record insert,multi record delete, multi record update for procedure and trigger
Posted

1 solution

It is all there in the T-SQL documentation:

Update (http://msdn.microsoft.com/en-us/library/ms177523.aspx[^]):
An update query is always effectively for multiple rows for a set of rows defined by the WHERE clause (or FROM clause join).
UPDATE table set field = value
will update every row in the table because there is no WHERE clause or join to limit the set.

Delete http://msdn.microsoft.com/en-us/library/ms189835.aspx[^]):
Delete is effectively the same as update with some small differences in syntax.
DELETE FROM table
will delete all rows in that table.

Insert (http://msdn.microsoft.com/en-us/library/ms174335.aspx[^]):
This is the exception as the simplest INSERT adds a single row and there is no concept of a WHERE clause for a simple INSERT (you are adding data so WHERE does not apply). To make an INSERT add multiple rows you must have either a sub-select clause to select the set to be added (which may have a WHERE clause and/or joins) or there is a form of the command to batch a series of effectively single row inserts(e.g.):
INSERT into table (value_A1, value_B1, value_C1), (value_A2, value_B2, value_C2)
inserts two rows.

You mention triggers. Triggers have special tables "inserted" and "deleted", insert triggers have the new rows in "inserted", delete triggers have the deleted rows in "deleted" and update triggers have the new values in "inserted" and the old values in "deleted" (unaltered values appear in both). You can perform joins using these special tables just as you would any other tables and they share the same schema as the table to which the trigger applies.

Examples using sub-clauses for multiple rows:
UPDATE table1
SET field1 = value
FROM table1 INNER JOIN table2 ON table1.JoinField1 = table2.JoinField2

DELETE 
FROM table1
<pre>WHERE FilterField IN (SELECT FilterField FROM table2)

INSERT INTO table1 (field1, field2, field3)
SELECT fielda, derivedvalueb, fieldc FROM table2
This covers the questions you raise in general. If you are having a specific problem, you need to share the code you tried and explain how the behaviour you desire differs from the outcome. Use the improve question function to add detail.

Good luck!
   
Comments
Maciej Los 28-Oct-14 12:08pm
   
Good job, Phil!
+5!
PhilLenoir 28-Oct-14 12:10pm
   
Thanks Maciej
Soundararajan O M 29-Oct-14 2:17am
   
thanks PhilLenoir your explanation is so clear and i got many ideas and views to work
once again thanks for this great work

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900