Databases
StackOverflow2013
Postsdbo
POFor Each UPDATE, INSERT Elsewhere Stored Procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
    primarykey
    data
    text
    <p>It may be the case this is only possible through looping, but who knows.</p> <p>I'm in a Java coding environment using MS SQL Server, and I have been searching around and can't seem to find a good way to INSERT records in say a transaction history table that we must keep updated for every action done in other tables upon issuing a single UPDATE command to UPDATE many records. For each UPDATE record updated, I would like to do an insert statement as well Essentially this is what we currently do, one by one, in code not using a stored procedure:</p> <pre><code>UPDATE table SET column1=null, column2=null, column3=0, column4=getDate() where column5=? and column6=1 ... //Insert transtable createTransaction(tran, localConn); ... INSERT INTO transtable ( a, b, c, d, e, f, g, h, i, " + "j, k, l, m, n, o, p, q, r, s, " + "t, u, v, w) " + " VALUES ( ? ,? ,? ,? ,? ,? ,getDate() ,getDate() ,getDate() ,? ,? ,? ,? ,? ,? ,? ,getDate() ,getDate(),? ,? ,? ,?,? ) </code></pre> <p>What I would like to do, is something like this instead for the UPDATE:</p> <pre><code>USE [DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[setFlags] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE table1 SET column1 = '2', column2 = NULL WHERE column1 = '1' or column1 = '2' --FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE UPDATE table2 SET column1 = '0', column2 = NULL WHERE column1 = '1' --FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE UPDATE table3 SET column1 = '0', column2 = NULL WHERE column1 = '1' --FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE END </code></pre> <p>However, for each record updated (usually about ~100 records would be updated using this utility), about 100 INSERTs need to be done as well in our transtable table, which will also contain some of the data from the rows being updated.</p> <p>Does anyone have any ideas? Resources out there? Etc.? I'm somewhat of a beginner, so sometimes I just don't know what to search for. Lately I've been searching "for each UPDATE INSERT SQL" or "looping insert for each update"</p> <p>I <em>might</em> need to do WHILE TSQL, but if there is a better way (it also sounds terribly inefficient), that'd be great to know: <a href="http://msdn.microsoft.com/en-us/library/ms178642.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms178642.aspx</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USaohm1989
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you looked at Triggers? Trigger are basically SQL code that is executed when stuff happens on a table. You can catch the rows that are being inserted/updated and for example, update another table. The only issue I could see is that it's global, meaning every update and every insert will proc the Trigger, not just your Stored Proc.
      singulars
      1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
      1. USAlexandre
    2. COYou may want to look up information about SQL Transaction Logs. Your server _may_ already be storing everything you're looking for. http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/
      singulars
      1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
      1. USDJ Quimby
    3. CONever heard of the Triggers Peekay, I'll give that one a look. The aspect of every update and every insert proccing a trigger may be an issue, but I'll still look into it.
      singulars
      1. POFor Each UPDATE, INSERT Elsewhere Stored Procedure
      1. USaohm1989
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload