- Insert Values
- Insert Select
- Insert Exec
- Select into
- Bulk Insert
Insert values is the most common way to insert data, the basic syntax is as follows, the name of the target column can be specified after the table name, and this explicitly specifies that the name of the target column is optional, but the benefit of this practice is to control the association between the column names of the columns of the statement, rather than simply relying on the definition table
The order in which each column appears
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 (0) ,--Month-int ',--Monthcn-varchar ( 0),--Ten-int ',--Te Ncn-varchar (0) ,--Week-int ',--weekday-varchar (0),- -day-int ' 2014-09-23 13:34:1 6 ' --date-datetime )
Determines the priority order of column values: Default value for the given value Null
Sql2008 enhanced the values feature to allow multiple records separated by commas in a single statement
INSERT into dbo. Table_1 (ID) VALUES (0), (1), (2)
Building a virtual table using values
SELECT * FROM (VALUES (0), (1), (2)) as a (ID)
Insert Select
Insert Select Inserts a set of result rows returned by the select query into the target table (the target table is a table that already exists, which differs from select into)
INSERT into dbo. Table_1 SELECT * FROM dbo. Table_2
Insert Exec
Insert Exec statement Inserts the result set returned by a stored procedure or dynamic SQL batch into the target table
Select into
the role of Select into is to create a target table and populate it with the returned result set, and you cannot use this statement to insert data into an existing table .
When a temporary table is created automatically when the specified target table is a temporary table, a user table is created when the user table is specified .
Select into copies the basic structure of the source table (including column names, data types, whether NULL is allowed, and the identity property) and does not replicate three things (constraints, indexes, triggers)
Another advantage of SELECT INTO: If the properties of the database are not set to complete recovery mode, select into will perform the operation in the smallest logging mode
Bulk Insert
This is a less useful way to import data from a file into a table that already exists
Example This example imports the order details from the specified data file, which uses the vertical bar (|) character as the field Terminator and |/n as the line terminator.
BULK INSERT northwind.dbo. [Order Details] From ' F:/orders/lineitem.tbl ' with ( fieldterminator = ' | ', rowterminator = ' |/n '
Inserting data into the SQL Server database