INSERT INTO (TSQL Statement)

inserts new records into the specified table.

Syntax

INSERT [ INTO ] <table_qname> [ ( column [ ,...n ] ) ]
    VALUES ( expression [ ,...n ] ) [ ,...n ]


INSERT [ INTO ] <table_qname> [ ( column [ ,...n ] ) ]
    <select_xtable>

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

Arguments

<table_qname>

is the qualified or unqualified name of the target table into which records will be inserted.

column [ ,…n ]

list of columns into which rows from VALUES clause or rows returned from SELECT expression will be inserted.

VALUES ( expression [ ,…n ] )

one or more rows that will be inserted into the table.

<select_xtable>

the records produced by this SELECT expression will be inserted into the table.

Remarks

The function @@ROWCOUNT returns the number of records inserted.

If a constraint is violated, e.g. duplicate in the primary key, an error is raised and no record is inserted.

Permissions

The user must have insert permission on the target table, and select permission on the referenced tables.

Example with IDENTITY Column

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

CREATE TABLE mytable (a INT IDENTITY NOT NULL, b VARCHAR(50) NULL);
GO

INSERT mytable VALUES ('Row #1'), ('Row #2');

INSERT INTO mytable (a, b)
    VALUES (-111, 'Row #3 with explicit identity value');

INSERT mytable VALUES ('Row #4');

SELECT *
FROM mytable;

The result is:

a          |b                                  |
-----------+-----------------------------------+
          1|Row #1                             |
          2|Row #2                             |
       -111|Row #3 with explicit identity value|
          3|Row #4                             |

(4 row(s) affected)

Example with SELECT Clause

IF OBJECT_ID ('mytable2', 'U') IS NOT NULL
    DROP TABLE mytable2;

CREATE TABLE mytable2 (col1 VARCHAR(100) NULL, col2 NUMERIC(12,2) NULL, col3 DATE NULL);
GO

INSERT INTO mytable2(col1, col2, col3)
SELECT UPPER(b), a*1.5, DATEADD(d, a, CAST('20160101' AS DATE))
FROM mytable
WHERE a<3;

SELECT * FROM mytable2;

The result is:

col1                               |col2           |col3      |
-----------------------------------+---------------+----------+
ROW #1                             |           1.50|2016-01-02|
ROW #2                             |           3.00|2016-01-03|
ROW #3 WITH EXPLICIT IDENTITY VALUE|        -166.50|2015-09-12|

(3 row(s) affected)