Using INSERT [TableName] SELECT .... syntax

You can use following syntax to insert into a table :

insert OrderMaster select
OrderMaster_date OrderMaster_id = 16 , = '2010-10-10' ,OrderMaster_customername='rajesh1' ,OrderMaster_createduser

To use such a syntax,

1.The number of columns must be equal to number of columns in the table
                AND
2.The sequence of columns must match the sequence of columns in table.

In any of the condition above is not satisfied following error will be thrown :

Column name or number of supplied values does not match table definition.


In fact, the names of columns given in SELECTcolname1='val1',.. doesnt matter. It straightly tries to match the columns with required
data types. e.g. if second column is DateTime, it looks for datetimeat the second place. If it is not found, an error is thrown (
Conversion failed when converting date and/or time from character string.
) 



Suppose, there are two varchar columns Col1 and Col2 in the table, in the sequence Col1 and Col2.  You execute a SELECT query like
following :

insert Table1
select col2-'col2val', col1='col1val'

You will find that after insertion, Col1 has a value of  'col2val' and Col2 has a value of 'col1val'.

Actually  the statement should have given an error , but it didn't because by chance the datatypes of interchanged columns were same. But in any case, the
order of columns specified in the table has been considerd valid.


But this all is definitely valid when a column list is specified :

insert OrderMaster (OrderMaster_id,OrderMaster_createduser,OrderMaster_customername,OrderMaster_date)select
OrderMaster_createduser OrderMaster_id = 20 , = 'rajesh1created' ,OrderMaster_customername='rajesh1customer' ,OrderMaster_date = '2010-10-10'
This  works perfectly

The same is applicable in case of  INSERT TableName Values () statement.

If no column list is specified, you must specify all columns  in order. and even nullable columns also need
to be specified.
If a column list is specified then, columns may not be in order, as also nullable columns can be ignored.



= 'rajesh1'