SQL Puzzle | Update one table & Insert in another table using single T-SQL statement at same time

The requirement is whenever we update Val column from Updates table we need to insert an entry in the INSERTS table. The challenge is to do it in a single SQL statement.

For details please check out the sample input and the expected output below-

Sample Inputs

UPDATES table

Inserts table will be empty in the beginning.

Expected Output

E.g. We have updated val column for Id = 1 from ‘First’ to ‘Four’. After the operation the expected data in respective tables will be-

UPDATES table

INSERTS Table

Rules/Restrictions

• The challenge is to do it in a single SQL statement.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

CREATE TABLE Updates
(
       ID TINYINT
       ,VAL VARCHAR(10)
)
GO

CREATE TABLE INSERTS
(
       UpdatedAt DATETIME
       ,NewValue  VARCHAR(10)     
)
GO


INSERT INTO Updates VALUES
(1,'First'),
(2,'Second')
GO

--

Solution 1

--

/*
** SOLUTION 1 
*/

INSERT INTO INSERTS
SELECT * FROM 
(
  UPDATE a
  SET a.Val = 'Four'  
  OUTPUT GETDATE(),Inserted.Val FROM UPDATES a WHERE ID = 1
)t(a,b)


--

Solution 2

--

/*
** SOLUTION 2
*/

UPDATE  Updates 
SET VAL='Four'
OUTPUT GETDATE(), Inserted.Val INTO INSERTS 
WHERE ID = 1


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com