InsertValuesInsertSelectInsertExecSelectIntoBulkInsertInsertValues is the most common method for data insertion. The basic syntax is as follows. You can specify the name of the target column after the table name. This explicitly specified name of the target column is optional, however, the advantage of this approach is that the association between column names of column values in the statement is
Insert Values Insert Select Insert Exec Select Into Bulk Insert Values is the most common method for data insertion. The basic syntax is as follows. You can specify the name of the target column after the table name, this explicitly specifying the name of the target column is optional, but the advantage of this method is that the association between the column names of the column values in the statement is as follows:
- Insert Values
- Insert Select
- Insert Exec
- Select
- Bulk Insert
Insert Values is the most common way to Insert data. The basic syntax is as follows. You can specify the name of the target column after the table name. This explicitly specified name of the target column is optional, however, the advantage of this approach is to control the association relationship between the column value columns of the statement, instead of simply relying on the definition of the table
Sequence of Columns
INSERT INTO dbo.DimDate ( DataKey , Year , Qu , QuCN , Month , MonthCN , Ten , TenCN , Week , WeekDay , Day , Date )VALUES ( 0 , -- DataKey - int 0 , -- Year - int 0 , -- Qu - int '' , -- QuCN - varchar(20) 0 , -- Month - int '' , -- MonthCN - varchar(20) 0 , -- Ten - int '' , -- TenCN - varchar(20) 0 , -- Week - int '' , -- WeekDay - varchar(20) 0 , -- Day - int '2014-09-23 13:34:16' -- Date - datetime )
Determine the priority order of column values: Default Value of the given value "Null
Sql2008 enhances the Values Function and allows you to specify multiple records separated by commas in a statement.
INSERT INTO dbo.Table_1 ( ID ) VALUES ( 0 ),( 1 ),( 2 )
Use Values to build a virtual table
SELECT * FROM (VALUES ( 0 ),( 1 ),( 2 )) AS a(id)
Insert Select
Insert Select inserts a group of result rows returned by the Select query Into the target table (the target table is an existing table, which is different from Select)
INSERT INTO dbo.Table_1 SELECT * FROM dbo.Table_2
Insert Exec
Insert ExecStatement can insert the result set returned by stored procedures or dynamic SQL batch processing to the target table
Select
SelectIs used to create a target table and fill it with the returned result set. You cannot use this statement to insert data to an existing table,
When the specified target table is a temporary table, a temporary table is automatically created, and a user table is created when the target table is a user table,
Select Into copies the basic structure of the source table (including the column name, data type, whether NUll is allowed, and the IDentity attribute). It does not copy three items (constraints, indexes, and triggers)
Another advantage of Select into: if the database attribute is not set to the recovery mode, Select into performs operations in the minimum logging mode.
Bulk Insert
This method is rarely used to import data from a file to an existing table.
In this example, order details are imported from the specified data file. The file uses the vertical bar (|) character as the field Terminator and the line terminator |/n.
BULK INSERT Northwind.dbo.[Order Details] FROM 'f:/orders/lineitem.tbl' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|/n' )