SQL Server series: INSERT statement

1. Insert Syntax

[With <common_table_expression> [,... n] ]INSERT {        [TOP (expression) [PERCENT] ]         [ into]         { <Object> |rowset_function_limited[With (<Table_Hint_Limited> [... n] ) ]        }    {        [(column_list)]         [<output clause>]        { VALUES( {DEFAULT | NULL |Expression}[,... N])[,... N]         |derived_table|execute_statement| <Dml_table_source>        | DEFAULT VALUES         }    }}[;]

In this structure, insert is the actual operation of the statement, the INTO keyword has no real meaning, the purpose is to enhance the readability of this statement. The INTO keyword is optional and it is recommended that you include the keyword in the statement. When you add data using the Entity Framework, the INSERT statement you execute does not use the INTO keyword.

2. Single insert

INSERT  into [dbo].[Product]    ([ProductName],[UnitPrice],[CreateDate])VALUES     ('LINQ to SQL', -,GETDATE());

3. Multiple Inserts

　　SQL Server 2012 supports inserting multiple rows of records one at a time, by adding additional comma-delimited insertion values when needed.

INSERT  into [dbo].[Product]    ([ProductName],[UnitPrice],[CreateDate])VALUES     ('LINQ to SQL', -,GETDATE()),    ('LINQ to Object', -,GETDATE());

4. INSERT into ... SELECT statement

When inserting multiple records at one time and the data that needs to be inserted is selected from another data source, you can use inert into ... SELECT statement.

The different data sources include:

◊ Another table in the database

◊ data table in another database on the same server

INERT into ... Select syntax:

INSERT  into <  table name > [<column list>] <  SELECT statement >

Example: inserting multiple records at a time from another database's data table as a data source

 UsePortalGOINSERT  into [dbo].[Product]    ([ProductName],[UnitPrice],[CreateDate])SELECT    [ProductName],[UnitPrice],[CreateDate] from     [Northwind].[dbo].[Product]GO

Example: Declaring a variable of type table, inserting more than one record at a time into a variable

 UsePortalGODECLARE @tbl TABLE(ProductNameVARCHAR( -)NULL, CreateDateDATETIME NULL)INSERT  into @tblSELECT    [ProductName],[CreateDate] from     [dbo].[Product]SELECT *  from @tblGO

5. References:

Http://msdn.microsoft.com/zh-cn/library/ms174335.aspx

SQL Server series: INSERT statement