DataBaser.Net: Output절의 사용

--DROP TABLE dbo.Employees
CREATE TABLE dbo.Employees
(
EmployeeID INT NOT NULL IDENTITY(1, 1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Status VARCHAR(20) DEFAULT 'Single'
) 

INSERT INTO dbo.Employees ( FirstName, LastName )
OUTPUT INSERTED.*
SELECT 'Susan', 'Kelley'
/*
EmployeeID  FirstName   LastName    Status
----------- ----------- ----------- --------------------
1           Susan       Kelley      Single
*/
GO

DECLARE @Employees TABLE 
(
	DeletedEmployeeID INT 
,	DeletedFirstName VARCHAR(50)
,	DeletedLastName VARCHAR(50)
,	InsertedLastName VARCHAR(50)
,	DeletedStatus VARCHAR(20)
,	DeletedDT DATETIME
) 

UPDATE dbo.Employees
SET LastName = 'Jones', Status = 'Married'
OUTPUT 
	DELETED.EmployeeID
,	DELETED.FirstName
,	DELETED.LastName
,	INSERTED.LastName -- !!!!!!!!!
,	DELETED.Status
,	GETDATE()
INTO @Employees
WHERE EmployeeID = 1

SELECT * FROM @Employees

create table #temp(seq int)

declare @out table
(
    seq int
)

insert @out
select seq
from (
    insert #temp 
    output inserted.*
    select number from master.dbo.spt_values
) t (seq)

select * from @out

insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
	merge --top (100) percent 
	target_table a using source_table b
	on a.id = b.id 
	when matched then 
		update set 
			a.id = b.id
		,	a.pw = b.pw
	when not matched by target then
		insert values (id, pw)
	when not matched by source then
		delete
	output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);

Contents