CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresforeach
aohm1989
aohm1989

Reputation: 401

For Each UPDATE, INSERT Elsewhere Stored Procedure

It may be the case this is only possible through looping, but who knows.

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:

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(),? ,? ,? ,?,? )

What I would like to do, is something like this instead for the UPDATE:

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

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.

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"

I might need to do WHILE TSQL, but if there is a better way (it also sounds terribly inefficient), that'd be great to know: http://msdn.microsoft.com/en-us/library/ms178642.aspx

Upvotes: 2

Views: 1193

Answers (2)

Gerardo Lima
Gerardo Lima

Reputation: 6713

Probably the best solution for your situation is to use a trigger, as MSDN defines: "A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.".

The following script creates two tables on tempdb: mydatanow and mydatalog. The trigger makelog runs every time a row is inserted or updated into mydatanow and its implementations copies all rows inserted into mydatanow to mydatalog -- if more than one row is affected by an update statement, all rows are copied.

USE tempdb;
GO
CREATE TABLE mydatanow (id SMALLINT, c1 VARCHAR(10), c2 VARCHAR(10));
CREATE TABLE mydatalog (id SMALLINT, c1 VARCHAR(10), c2 VARCHAR(10), moment DATETIME);
GO
CREATE TRIGGER makelog ON mydatanow
AFTER INSERT, UPDATE
AS BEGIN
    INSERT mydatalog(id, c1, c2, moment)
    SELECT id, c1, c2, GETDATE()
    FROM inserted
END
GO
INSERT mydatanow VALUES (1, 'abe', 'apple');
INSERT mydatanow VALUES (2, 'beth', 'banana');
UPDATE mydatanow SET c1 = 'carl', c2 = 'no fruit';
GO
SELECT * FROM mydatanow;
-- id     c1         c2
-- ------ ---------- ----------
-- 1      carl       no fruit
-- 2      carl       no fruit

SELECT * FROM mydatalog;
-- id     c1         c2         moment
-- ------ ---------- ---------- -----------------------
-- 1      abe        apple      2012-05-10 19:36:45.843
-- 2      beth       banana     2012-05-10 19:36:45.843
-- 1      carl       no fruit   2012-05-10 19:36:45.847
-- 2      carl       no fruit   2012-05-10 19:36:45.847

Upvotes: 1

David Brabant
David Brabant

Reputation: 43539

You don't tell what version of SQL Server you are using, neither what edition. If you are on 2008 Enterprise edition, have a look at Change Data Capture. There is a good introduction article here.

Upvotes: 2

Related Questions