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

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 EXEC Statement

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