1 Something about the insert

OUTPUT syntax

Create base table for testing

1. USE AdventureWorks;
2. CREATE TABLE tOUTPUT
3. (SID int IDENTITY, EName varchar(256), ModifiedDate datetime)
4. GO

Use INSERTED to retrieve newly insert data. suser_name() is used to return who is running the insert.

1. INSERT INTO tOUTPUT(EName, ModifiedDate)
2.     OUTPUT INSERTED.SID, suser_name(), INSERTED.ModifiedDate
3. VALUES('Jack', getdate())

Use INSERTED.*  to retrieve all the insert data

1. INSERT INTO tOUTPUT(EName, ModifiedDate)
2.     OUTPUT suser_name(), INSERTED.*
3. VALUES('Tom', getdate())

Declare table variable and store the result in the table variable

1. DECLARE @InserttOUTPUT TABLE
2.     (SID int, InsertedBy varchar(256), ModifiedDate datetime)
3.     
4. INSERT INTO tOUTPUT(EName, ModifiedDate)
5.     OUTPUT INSERTED.SID, suser_name(), INSERTED.ModifiedDate
6.         INTO @InserttOUTPUT
7. VALUES('Bob', getdate())    
8.  
9. SELECT * FROM @InserttOUTPUT

Declare table variable and store the value changes, before and after update

1. DECLARE @NameChangeDetails TABLE
2.     (SID int,OldName varchar(256),NewName varchar(256),UpdatedBy varchar(256))    
3.  
4. UPDATE tOUTPUT
5. SET EName = 'Jam'
6.     OUTPUT INSERTED.SID, DELETED.EName, INSERTED.EName, suser_name()
7.         INTO @NameChangeDetails
8. WHERE SID = 1    
9. SELECT * FROM @NameChangeDetails

Declare the table variable store the delete data

1. DECLARE @NameDeleteDetail TABLE
2.     (SID int,DeleteName varchar(256), UpdatedBy varchar(256))    
3.  
4. Delete tOUTPUT
5.     OUTPUT DELETED.SID, DELETED.EName, suser_name()
6.         INTO @NameDeleteDetail
7. WHERE SID = 1    
8. 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

1. USE AdventureWorks;
2. CREATE TABLE myRowConstructors
3. ( Name nvarchar(30),ListPrice money)
4. GO

Inset multiple rows in one DML statement.

1. INSERT dbo.myRowConstructors
2.     VALUES (N'Test1',10), (N'Test2',20), (N'Test3',30)

We can also insert the rows base on the select statement

1. INSERT INTO dbo.myRowConstructors (Name, ListPrice)
2. VALUES ('Helmet', 25),
3.        ('Wheel', 35),
4.        ((SELECT Name FROM Production.Product WHERE ProductID = 720),
5.         (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
6. GO

Lets exam the data

1. 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.

1. DBCC SQLPERF(LOGSPACE);

Create the base table

1. USE AdventureWorks;
2. CREATE TABLE tbLogInfo
3. (DB sysname, logsize float, used float, status int)
4. GO

Insert the data

1. INSERT tbLogInfo
2. EXECUTE('DBCC SQLPERF(LOGSPACE);')

Lets Query the tbLogInfo

1. 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:

1. USE AdventureWorks;
2. SELECT top 10 *
3.     INTO #temp1
4. FROM HumanResources.Department
5. SELECT top 10 *
6.     INTO ##temp2
7. FROM HumanResources.Employee

Run Query against #temp1 and ##temp2

1. SELECT * FROM #temp1
2. go
3. SELECT * FROM ##temp2
4. 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