Note-microsoft SQL Server 2008 Tech Insider: T-SQL Language Basics-08 data modification

Source: Internet
Author: User
Tags bulk insert

Inserting data

T-SQL provides several data-inserted statements: Insert VALUES, insert SELECT, insert EXEC, SELECT into, and BULK insert.

INSERT Values Statement:

INSERT  into dbo. Orders (OrderID, OrderDate, Empid, CustID)  VALUES(10001'20090212'  3'A');

SQL Server 2008 enhances the functionality of the values statement, allowing you to specify a comma-separated multiline record in a single statement:

INSERT  intodbo. Orders (OrderID, OrderDate, Empid, CustID)VALUES  (10003,'20090213',4,'B'),  (10004,'20090214',1,'A'),  (10005,'20090213',1,'C'),  (10006,'20090215',3,'C');

You can also use values to build virtual tables in SQL Server 2008:

SELECT * from(VALUES         (10003,'20090213',4,'B'),         (10004,'20090214',1,'A'),         (10005,'20090213',1,'C'),         (10006,'20090215',3,'C') )      asO (OrderID, OrderDate, Empid, CustID);

INSERT SELECT statement, note that the target table must exist beforehand.

INSERT  into dbo. Orders (OrderID, OrderDate, Empid, CustID)  SELECT  OrderID, OrderDate, Empid, CustID    from TSQLFundamentals2008.Sales.Orders   WHERE = ' UK ';


INSERT  into dbo. Orders (OrderID, OrderDate, Empid, CustID)  EXEC@country='France  ';

SELECT into statement

SELECT OrderID, OrderDate, Empid, CustID  into dbo. Orders from TSQLFundamentals2008.Sales.Orders;

The SELECT into statement copies the underlying structure of the source table (including column names, data types, whether null and identity attributes are allowed), and data. Constraints, indexes, and triggers are not copied from the source table.

BULK INSERT statement

BULK INSERTDbo. Orders from 'C:\temp\orders.txt'   with(DataFileType= 'Char', FieldTerminator= ',', Rowterminator= '\ n'    );
Data updates with the top option

The following code deletes 50 orders with a minimum order ID value instead of randomly deleting 50 rows:

 with  as (  SELECTTOP(from    dbo). Orders  ORDER by OrderID)DELETE from C;
Output clause
INSERT  into dbo. T1 (datacol)  OUTPUT inserted.keycol, Inserted.datacol    SELECT  lastname    from  TSQLFundamentals2008.HR.Employees    WHERE= N'USA ';

The OUTPUT clause returns a row for each row that has been modified. If there is a requirement, for auditing purposes, you need to import a subset of the modified rows into a table:

INSERT  intodbo. Productsaudit (ProductID, ColName, Oldval, newval)SELECTProductID, N'UnitPrice', Oldval, newval from(UPDATEdbo. ProductsSETUnitPrice*= 1.15OUTPUT Inserted.productid, Deleted.unitprice asOldval, Inserted.unitprice asnewvalWHERESupplierID= 1) asDWHEREOldval< 20.0  andnewval>= 20.0;

Note-microsoft SQL Server 2008 Tech Insider: T-SQL Language Basics-08 data modification

Related Article

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: 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.