OUTPUT clause can be used in DML statements - SQL Server 2008

milani_lucie
milani_lucie used Ask the Experts™
on
Hi,

Can you please let me know about this new feature:

OUTPUT clause can be used in DML statements.- SQL Server 2008

Please provide me simple example on this ?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This link explains a little bit about it: http://msdn.microsoft.com/en-us/library/ms177564.aspx
AneeshDatabase Consultant
Top Expert 2009

Commented:
declare @Tab table (i int )
insert into @Tab
output inserted.i
SELECT 20

Author

Commented:
Here is what i have. Can you please explain the below program:

DECLARE @names TABLE
(
      NameId      int identity not null,
      Name      varchar(20) not null
)

INSERT INTO @names (Name)
SELECT o.Name FROM
(
      INSERT INTO @names (Name)
      OUTPUT inserted.Name
      VALUES  ('Steve'), ('Nick')
) AS o

SELECT * FROM @names

Thanks

Author

Commented:
aneeshattingal:

Please explain about in NESTED DML statements.

Thanks
Commented:
Look for comments 1 to 4 below.

--1. This declares a table variable, which we'll be populating.
DECLARE @names TABLE
(
      NameId      int identity not null,
      Name      varchar(20) not null
)

--3. (Did you see Comment 2 below?)
--3 (cont). So now we're taking the two rows outputted from our subquery, and inserting them into @names. This means we're putting them in TWICE
INSERT INTO @names (Name)
SELECT o.Name FROM
(
      --2. This inserts two rows into the @names table, and outputs the Name field of each inserted row, to be used in the outer query in a table called o.
      INSERT INTO @names (Name)
      OUTPUT inserted.Name
      VALUES  ('Steve'), ('Nick')
) AS o

--4. Having done the inserts (two rows, twice), we retrieve the four rows.
SELECT * FROM @names

...but I think a better example would have been to insert the NameID field into a separate table...

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial