Thursday, April 8, 2010

INSERT Statement Syntax

0 comments
Adds a new row to a table or a view.

Examples
1. Use a simple INSERT

Insert into table_name VALUES (1, 'Row #1')

2.Insert data that is not in the same order as the columns

Insert into table_name (column_2, column_1) VALUES ('Row #1',1)

3. Insert data with fewer values than columns

i. Insert into table_name (column_4) VALUES ('Explicit value')
ii. Insert into table_name (column_2,column_4) VALUES ('Explicit value', 'Explicit value')
iii.Insert into table_name (column_2,column_3,column_4) VALUES ('Explicit value',-44,'Explicit value')

4.Load data into a table with an identity column

SET IDENTITY_INSERT T1 ON INSERT INTO T1 (column_1,column_2) VALUES (-99,'Explicit identity value')

5 .Insert data using the TOP clause in a SELECT statement

INSERT INTO new_authors SELECT TOP 10 * FROM authors

6. Insert data using default values options

insert into table_name default values

0 comments: