Feeds:
Posts
Comments

Posts Tagged ‘change data’


-- Please note this is not my work.
-- Code cpoied and altered from:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/#

 

IF OBJECT_ID ('tempdb..#books') IS NOT NULL
DROP TABLE #books;

CREATE TABLE #books
(
BookID int NOT NULL PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL
);

 

-- declare @InsertOutput1 table variable

DECLARE @InsertOutput1 table
(
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime
);

-- insert new row into #books table
INSERT INTO #books
OUTPUT INSERTED.*
INTO @InsertOutput1
VALUES(106, 'One Hundred Years of Solitude', GETDATE());

-- view inserted row in #books table
--SELECT * FROM #books;

-- view output row in @InsertOutput1 variable
SELECT 'INSERT', * FROM @InsertOutput1;

 

DECLARE @UpdateOutput2 table
(
OldBookID int,
NewBookID int,
BookTitle nvarchar(50),
OldModifiedDate datetime,
NewModifiedDate datetime,
DiffInSeconds int
);

-- update row in #books table
UPDATE #books
SET
BookID = BookID + 1,
ModifiedDate = GETDATE()
OUTPUT
DELETED.BookID,
INSERTED.BookID,
INSERTED.BookTitle,
DELETED.ModifiedDate,
INSERTED.ModifiedDate,
DATEDIFF(ss, DELETED.ModifiedDate, INSERTED.ModifiedDate)
INTO @UpdateOutput2
WHERE BookTitle = 'One Hundred Years of Solitude';

-- view updated row in #books table
--SELECT * FROM #books;

-- view output row in @UpdateOutput2 variable
SELECT 'UPDATE', * FROM @UpdateOutput2;

 

DECLARE @DeleteOutput1 table
(
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime
);

-- delete row in #books table
DELETE #books
OUTPUT DELETED.*
INTO @DeleteOutput1
WHERE BookID = 107;

-- view updated row in #books table
-- SELECT * FROM #books;

-- view output row in @DeleteOutput1 variable
SELECT 'DELETE', * FROM @DeleteOutput1;

Read Full Post »