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 fromSELECT
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)