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===