OUTPUT CLUASE, Sql Server

OUTPUT CLUASE

-- Insert With Output
-- Insert With Output
-- Insert With Output
-- Insert With Output
-- Insert With Output
-- Insert With Output

create table MyOrders(
orderid INT NOT NULL identity(1,1) PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
)

create table SomeTable(
orderid INT NOT NULL identity(1,1) PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
)

DECLARE @Orders AS TABLE(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
shipcountry varchar(100)
);

INSERT INTO @Orders(orderid, custid, empid, orderdate,shipcountry) VALUES
    (2, 1, 3, '20120612','Norway'),
    (3, 2, 2, '20120612','Norway'),
    (4, 3, 5, '20120612','India');

--Get Inserted Data using output cluase
    insert into MyOrders (custid,empid,orderdate)
    output inserted.orderid,inserted.custid,inserted.orderdate
    select custid,empid,orderdate from @Orders
    
    
    -- insert data in other table using output and into cluase
    INSERT INTO MyOrders(custid, empid, orderdate)
    OUTPUT
    inserted.custid, inserted.empid, inserted.orderdate
    INTO SomeTable(custid, empid, orderdate)
    SELECT custid, empid, orderdate
    FROM @Orders
    WHERE shipcountry = N'Norway';

select * from SomeTable

select * from MyOrders

-- Delete with output
    DELETE FROM MyOrders
    OUTPUT deleted.orderid
    WHERE empid = 2;
    
    
    -- Update with Output
    UPDATE MyOrders
    SET orderdate = DATEADD(day, 1, orderdate)
    OUTPUT
    inserted.orderid,
    deleted.orderdate AS old_orderdate,
    inserted.orderdate AS neworderdate
    WHERE empid = 3;


run \| edit \| history \| help	0

Please log in to post a comment.