Insert data to SqlServer Database

Source: Internet
Author: User
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:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.