--DROP TABLE testdata

CREATE TABLE TestData
(
id INT IDENTITY PRIMARY KEY,
data INT
)

INSERT INTO testdata 
OUTPUT INSERTED.id AS [Inserted id],
INSERTED.data AS [Inserted data]
VALUES (20)
PRINT SCOPE_IDENTITY()



DECLARE @newData TABLE(id INT, data INT)
INSERT Scratch.dbo.TestData
OUTPUT INSERTED.id AS [Inserted id], INSERTED.data AS [Inserted data]
INTO @newData
SELECT * 
  FROM OPENROWSET (
    BULK 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\data.dat',
    FORMATFILE = 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\format.xml')
    AS d	
SELECT * FROM @newData



INSERT Scratch.dbo.TestData
OUTPUT INSERTED.*
SELECT * 
  FROM OPENROWSET (
    BULK 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\data.dat',
    FORMATFILE = 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\format.xml')
    AS d	





DECLARE @maxID INT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @maxId = MAX(id) FROM testdata
INSERT Scratch.dbo.TestData
--OUTPUT INSERTED.id AS [Inserted id], INSERTED.data AS [Inserted data]
SELECT * 
  FROM OPENROWSET (
    BULK 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\data.dat',
    FORMATFILE = 'c:\Dan Docs\NewSql2005Book\(08)tsql\Samples\output\format.xml')
    AS d	
SELECT * from TestData where id > @maxID
COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


TRUNCATE table testdata

select * from testdata

UPDATE TestData
OUTPUT 


ROLLBACK TRAN


UPDATE TestData
SET data = data + 20
OUTPUT INSERTED.id,
DELETED.data AS [old data], 
INSERTED.data AS [new data]
WHERE data>100

DELETE TestData
OUTPUT DELETED.id, DELETED.data
WHERE data > 100

INSERT TestData 
OUTPUT INSERTED.id, INSERTED.data
VALUES (100)


INSERT TestData



select * from testdata

--DROP TABLE @T

DECLARE @T table(id int, data int)
INSERT 
INTO testdata 
OUTPUT inserted.id, inserted.data INTO @T
VALUES (20)
SELECT * from @T

SELECT * from testdata 
where id + 1 in 
(
INSERT 
INTO testdata 
OUTPUT inserted.id
VALUES (20)

)

WITH A
as
(
INSERT 
INTO testdata 
OUTPUT inserted.id
VALUES (20)
)
SELECT * from testdata 
where id + 1 in 
( select id from A)


INSERT 
INTO testdata 
OUTPUT inserted.id AS A, inserted.data AS B
VALUES (20)



DECLARE @T table(inew int, iold INT)
UPDATE testdata
SET I = I + 1
OUTPUT inserted.i , deleted.i INTO @T
 where I > 20
SELECT * from @T


DECLARE @T table(inew int, iold INT)
UPDATE testdata
SET I = I + 1
OUTPUT inserted.i as inew, deleted.i as iold
 where I > 20
SELECT * from @T


CREATE TABLE #tdata (inew INT, iold INT)

DECLARE @T table(inew int, iold INT)
UPDATE testdata
SET I = I + 1
OUTPUT inserted.i, deleted.i INTO #tdata
 where I > 20
SELECT * from #tdata


