Inserting data into the SQL Server database

Original: Inserting data into SQL Server database

1. Insert Values
2. Insert Select
3. Insert Exec
4. Select into
5. 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