Update records and record the updated records into a table

Question

Update records and record the updated records into a table

TollHouse

Old Hand

Points: 331
More actions
March 17, 2017 at 6:56 am

#401798

Greetings,
I am a Systems Engineer turned DBA. I am starting to understand the basics of this new job role and struggle on some next level tasks. I have been tasked with creating a job that will identify accounts that have been locked out in my database. Once I identify those accounts. I will unlock them. That part I have done without issue. My next steps would be to record the changes into a table. So it would be like this.
I would like to have this run as a daily job.
1. Identify locked out accounts.
2. Update any accounts identifed. Set count lfag to 0 (zero)
3. Insert into Audit table those records updated.
Steps 1 and 2 are easy enough. Step 3 is beyond my current understanding. Below is an example of the t-sql I am using for this. Any guidance or assistance would be awesome.
Insert into Attemptreset(CustomerId, attempt)
Select CustomerNumber, count
from Table1
where Count = 5 and updated > (DATEADD(Day,-40,getdate()))
go
--Begin Tran
Update Table1
Set Count = 0
FROM Table1 AS T1 JOIN Attemptreset AS ar ON T1.CustomerNumber = ar.CustomerId
Where T1.CustomerNumber = ar.CustomerId
drop table Attemptreset

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Use the OUTPUT statement
UPDATE Table1 SET Count = 0 OUTPUT inserted.* --Or list your columns, i.e. inserted.CustomerName, inserted.Postcode, etc, etc INTO #Output FROM Table1 AS T1 JOIN Attemptreset AS ar ON T1.CustomerNumber = ar.CustomerId WHERE T1.CustomerNumber = ar.CustomerId
You'll need to define your #Output table before hand. you can also only chose to return specific columns, instead of all (*) in the OUTPUT statement

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

TollHouse Old Hand Points: 331 More actions · Answer 2

Thom A,

Thank you so much! This worked like a champ and pointed me to a new command. This has been something that has driven me crazy to figure out. My searches have turned up no information or options way outside of my skill set. It seems I didn't know the magic words to look up. I looked up the OUTPUT use and found a ton of real information. Once again SQLServerCentral has come to my rescue and conintues to teach me to be a better DBA.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

I make people bring me my favorite sandwich (JJ's #9, no mayo, no onions) before I'll unlock their account. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

With Composable DML, you can wrap the update call with the insert call, and avoid the #temp table completely.
Demo code (creates sample DB and table first):

USE MASTER;
GO
CREATE DATABASE testr;
GO
USE testr ;
GO
-- create a sample table, with a Count columns that defaults to a random value between 0 and 5
CREATE TABLE dbo.Table1(CustomerNumber int NOT NULL PRIMARY KEY, CustomerName varchar(256), 
        [Count] smallint NOT NULL DEFAULT(ROUND(ABS(CHECKSUM(newid())), 0) % 6));
GO
-- dummy data... need some IDs and names
INSERT dbo.Table1(CustomerNumber, CustomerName)
SELECT object_id, [name] FROM sys.objects;
GO
CREATE TABLE dbo.AuditUserResets(ResetID int IDENTITY(1, 1) NOT NULL PRIMARY KEY, CustomerID int NOT NULL, 
           OrigCount smallint NOT NULL, NewCount smallint NOT NULL,
           ResetTimeStamp datetime NOT NULL DEFAULT(getdate()));
GO
-- match the OP pattern: source of rows to update is a separate table
CREATE TABLE dbo.Attemptreset(CustomerID int NOT NULL PRIMARY KEY);
GO
INSERT dbo.Attemptreset(CustomerID)
SELECT CustomerNumber FROM dbo.Table1 WHERE [Count] > 4;
GO

Here's the actual technique:


-- Use Composable DML to apply the results of one statement to a different target
-- the outer statement inserts the results of the inner statement to a different table
  INSERT dbo.AuditUserResets(CustomerID, OrigCount, NewCount)
  SELECT ResetResults.CustomerNumber, ResetResults.OrigCount, ResetResults.NewCount
   FROM (
     -- this inner query performs the first step: updating users
     UPDATE res
      SET [Count] = 0
     OUTPUT inserted.CustomerNumber, deleted.[Count], inserted.[Count]
      FROM dbo.Table1 res 
     WHERE EXISTS(SELECT * FROM dbo.Attemptreset ar WHERE res.CustomerNumber = ar.CustomerID)

) AS ResetResults(CustomerNumber, OrigCount, NewCount) -- ResetResults is the table alias for the OUTPUT rows, and the names (CustomerNumber, OrigCount, NewCount) are -- explicitly specified in the AS clause GO SELECT * FROM dbo.AuditUserResets; GO

-Eddie

Eddie Wuerch
MCM: SQL