SQL INSERT Statement Syntax and Examples

Description

Adds a new row to a table or a view.

Syntax

INSERT [ INTO]  { table_name  | view_name } [ ( column_list ) ]
VALUES  ( expression [ ,...n] )

OR

INSERT [ INTO]  { table_name  | view_name } [ ( column_list ) ]
select_statement

Example

INSERT table1 (c1)
VALUES (9)

OR

INSERT table1 (c1)
SELECT c1
FROM table2

Detail

INSERT appends new rows to a table. To replace data in a table, the DELETE or TRUNCATE TABLE statements must be used to clear existing data before loading new data with INSERT. To modify column values in existing rows, use UPDATE. To create a new table and load it with data in one step, use the INTO option of the SELECT statement.

INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.