T-SQL – Inserts

Simple inserts

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) 
VALUES(2, 19, '20120620', N'USA', 30.00); --explicitly select columns

INSERT INTO Sales.MyOrders
VALUES(2, DEFAULT, '20120620', N'USA', 30.00); --insert data to all existing columns

SET IDENTITY_INSERT Sales.MyOrders ON; -- when you want to insert data into identity column (alter table privilege neccessary)
INSERT INTO Sales.MyOrders (orderid, custid, empid, orderdate, shipcountry, freight)
VALUES(9999, 2, 19, '20120620', N'USA', 30.00);
SET IDENTITY_INSERT Sales.MyOrders OFF;

Multiple inserts

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES
(2, 11, '20120620', N'USA', 50.00),
(5, 13, '20120620', N'USA', 40.00),
(7, 17, '20120620', N'USA', 45.00); -- insert a few rows at the same time

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
SELECT custid, empid, orderdate, shipcountry, freight
FROM Sales.Orders
WHERE shipcountry = N'Norway'; -- inserts query results into existing table

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
EXEC Sales.OrdersForCountry @country = N'Portugal'; -- inserts function results into existing table

SELECT orderid, custid, orderdate, shipcountry, freight
INTO Sales.MyOrders
FROM Sales.Orders
WHERE shipcountry = N'Norway'; --creates new table and copies rows, it won't copy constraints
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s