SQL Commands Lookup : INSERT

I decide to keep some notes here. It may help you as well.

==========Insert Statements=========================
--You can insert records into table without specifying the column names.
INSERT INTO table_name

VALUES (value1, value2, value3,...)

It may be better to specify columns for some cases.
INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)


--Multiple records can be inserted as well. SQL Server 2008 has "values" clause.

insert into dbo.myorders (orderid, orderdate, empid, custid)
values
(102, '20090212', 4, 'B'),
(103, '20090212', 1, 'A'),
(104, '20090212', 5, 'C'),
(105, '20090212', 6, 'D');

You can create this table to test it.

create table dbo.myorders

(
  orderid int not null
  constraint PK_myorders primary key,
  orderdate date not null
  constraint dft_orderdate default(current_timestamp),
 empid int not null,
 custid varchar(10) not null
 )

==Insert Select statement inserts the rows returned by SELECT query into a target table.

insert into dbo.myorders (orderid, orderdate, empid, custid)
SELECT orderid, orderdate, empid, custid
from SomeOrderTable
where shipcountry = 'USA';

This query will insert all selected records into our table.

==Insert Exec statement is similar to "insert select", but you can insert result set returned from sql procedure. We will create a simple stored proc to show this.

create PROC proc_getorders

   @custid as int
as

select orderid, orderdate, empid, custid
from Sales.Orders
where custid = @custid;
go

This proc will return a table for a given customer id. We can insert returned result from stored proc easily with "Insert ...exec" statement.
INSERT INTO dbo.myorders(orderid, orderdate, empid, custid)
  EXEC dbo.proc_getorders 5;

===Insert with "SELECT INTO"
You can use to create copy of the tables. This statement copies column names, null cases, identity, and types from source table. However, it does not copy constraints, indexes, and triggers.

SELECT orderid, orderdate, empid, custid
INTO dbo.myorder_copy
from dbo.myorder

You are not going to have primary key and default value constraint at your myorder_copy table.
Bulk insert dbo.myorders from 'C:\SomeDirectory\myorderdata.txt'

with
(
 datafiletype = 'char',
 fieldterminator = ',',
 rowterminator = '\n'
);
You can create a txt file similar to this:
2003,20090102,1,3
2004,20090102,1,6

====Insert and IDENTITY  property====
I showed examples without identity column. If you have columns with autogenerated type, you can skip that field when inserting data.
You can make another table with auto generated column. IDENTITY(2,3) will set starting number to 2 and increment to 3. You need to create constraints with different names, so add something to their name.

create table dbo.myorderAutoID

(
orderid int not null identity(1,1),
constraint PK2_myorders primary key,

 orderdate date not null
constraint dft2_orderdate default(current_timestamp),
empid int not null,
custid varchar(10) not null
)

Now, you can add records. Table will have auto generated order id.

insert into dbo.myorderAutoID ( orderdate, empid, custid)

values
( '20090212', 4, 'B'),
(  '20090212', 4, 'B'),

(  '20090212', 4, 'B');

==@@identity and SCOPE_IDENTITY() will return last generated identity value in current session. If you call those after your insert operation, it will give last identity value.
Run "Select @@identity; " to see the last generated identity value. If you want to get last identity value produced, you can use IDENT_CURRENT function. It is related to the table.

Select IDENT_CURRENT('dbo.myorderAutoID') will return 3, because you entered only 3 records to the table.

If these records are not inserted for some reason, table will skip the identity value. If your record is rejected and not inserted, table will not use that id.


insert into dbo.myorderAutoID ( orderdate, empid, custid)
values
(null , 4, 'B');

This query will skip the identity 4 and next inserted row will have identity value of 5.

===End of Insert===

SQL Coalesce

If you want to convert null fields into something else inside your select command, you can do that with "Coalesce".

select

custid, coalesce( region, ' ')  as region
from Sales.Customers
order by region;

This SQL query will show null regions as space.

My first post!

CROSS APPLY
Examples are based on AdventureWorks DB. You can find that from MSDN and install that with your express version of sql server 2008. I will post links about how to setup that.

I like the "Cross Apply" command in MS SQL. It gives you access to left side of the join and you can perform operations for each row from left side.

Normally cross join operator performs cartesion product if you don't specify where statement in your query.

USE AdventureWorks;
GO
Select p.SalesPerson, st.Name as Territory
from Sales.SalesPerson p
cross join Sales.SalesTerritory st

This query will produce total of nxm rows. You don't have access to table p inside table st.

You can do much better with Cross Apply.

1- This will calculate avg qty in orders and total number of orders for each product.

select pt.ProductID, pt.Name, pt.StandardCost, A.num_orders, A.avg_order
from Production.Product pt
cross apply(
select count(distinct sd.SalesOrderID) as num_orders, avg(sd.OrderQty) as avg_order
from Sales.SalesOrderDetail as sd
where sd.ProductID = pt.ProductID

) as A
order by num_orders desc;

2-Latest 3 orders per customer

Select c.CustomerID, A.SalesOrderID, A.orderdate

from Sales.Customer as c
cross apply(
   select top(3) O.SalesOrderID, O.SalesPersonID, O.OrderDate,O.DueDate
   from sales.SalesOrderHeader as O
   where O.CustomerID = c.CustomerID
   order by orderdate desc, SalesOrderID desc
) as A;

3-Other examples......