OUTPUT syntax
Create base table for testing
- USE AdventureWorks;
- CREATE TABLE tOUTPUT
- (SID int IDENTITY, EName varchar(256), ModifiedDate datetime)
- GO
Use INSERTED to retrieve newly insert data. suser_name() is used to return who is running the insert.
- INSERT INTO tOUTPUT(EName, ModifiedDate)
- OUTPUT INSERTED.SID, suser_name(), INSERTED.ModifiedDate
- VALUES('Jack', getdate())
Use INSERTED.* to retrieve all the insert data
- INSERT INTO tOUTPUT(EName, ModifiedDate)
- OUTPUT suser_name(), INSERTED.*
- VALUES('Tom', getdate())
Declare table variable and store the result in the table variable
- DECLARE @InserttOUTPUT TABLE
- (SID int, InsertedBy varchar(256), ModifiedDate datetime)
- INSERT INTO tOUTPUT(EName, ModifiedDate)
- OUTPUT INSERTED.SID, suser_name(), INSERTED.ModifiedDate
- INTO @InserttOUTPUT
- VALUES('Bob', getdate())
- SELECT * FROM @InserttOUTPUT
Declare table variable and store the value changes, before and after update
- DECLARE @NameChangeDetails TABLE
- (SID int,OldName varchar(256),NewName varchar(256),UpdatedBy varchar(256))
- UPDATE tOUTPUT
- SET EName = 'Jam'
- OUTPUT INSERTED.SID, DELETED.EName, INSERTED.EName, suser_name()
- INTO @NameChangeDetails
- WHERE SID = 1
- SELECT * FROM @NameChangeDetails
Declare the table variable store the delete data
- DECLARE @NameDeleteDetail TABLE
- (SID int,DeleteName varchar(256), UpdatedBy varchar(256))
- Delete tOUTPUT
- OUTPUT DELETED.SID, DELETED.EName, suser_name()
- INTO @NameDeleteDetail
- WHERE SID = 1
- SELECT * FROM @NameDeleteDetail
Row constructors ( Table Value Contractors)
Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement.
The maximum number of rows that can be constructed using the table value constructor is 1000.
Create the base table
- USE AdventureWorks;
- CREATE TABLE myRowConstructors
- ( Name nvarchar(30),ListPrice money)
- GO
Inset multiple rows in one DML statement.
- INSERT dbo.myRowConstructors
- VALUES (N'Test1',10), (N'Test2',20), (N'Test3',30)
We can also insert the rows base on the select statement
- INSERT INTO dbo.myRowConstructors (Name, ListPrice)
- VALUES ('Helmet', 25),
- ('Wheel', 35),
- ((SELECT Name FROM Production.Product WHERE ProductID = 720),
- (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
- GO
Lets exam the data
- Select * from myRowConstructors
INSERT EXECUTE
Insert the table base on the execute result of store procedure.
Below example is creating the table and insert the data about the Log space.
- DBCC SQLPERF(LOGSPACE);
Create the base table
- USE AdventureWorks;
- CREATE TABLE tbLogInfo
- (DB sysname, logsize float, used float, status int)
- GO
Insert the data
- INSERT tbLogInfo
- EXECUTE('DBCC SQLPERF(LOGSPACE);')
Lets Query the tbLogInfo
- select * from tbLogInfo
Temporary table
- # session level temporary table: Only exist on the current session.
- ## Global temporary table: Can be Query from other session
Test sample as below:
- USE AdventureWorks;
- SELECT top 10 *
- INTO #temp1
- FROM HumanResources.Department
- SELECT top 10 *
- INTO ##temp2
- FROM HumanResources.Employee
Run Query against #temp1 and ##temp2
- SELECT * FROM #temp1
- go
- SELECT * FROM ##temp2
- go
Open another session from sqlcmd and run the same query.
We will get the
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temp1'.
Reference
Table Value Constructor http://msdn.microsoft.com/en-us/library/dd776382.aspx
0 comments:
Post a Comment