Основы t-sql. dml

The INSERT Statement

The statement allows you to add data to your database tables. The syntax goes like this:

INSERT INTO table_name (col_1, col_2, col_3)
VALUES (value_1, value_2, value_3);

This inserts data into one row. The order of the values provided must correspond with the columns that the values are to be inserted into.

If you are inserting data into all columns, you can omit the column names and just do this:

INSERT INTO table_name VALUES (value_1, value_2, value_3);

To populate multiple rows, use a comma to separate each row, like this:

INSERT INTO table_name
VALUES
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3),
(value_1, value_2, value_3);

The above example populates 4 rows and assumes there are 3 columns in each row.

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

If you are adding values for all the columns of the table, you do not need to
specify the column names in the SQL query. However, make sure the order of the
values is in the same order as the columns in the table. The INSERT INTO syntax
would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, …);

Особенности

Во время выполнения оператора могут возникнуть ошибки:

• если при создании таблицы для поля был указан параметр not null и не было определено значение по умолчанию (см. create), то при отсутствии для него вставляемого значения возникнет ошибка. Решение очевидно:
  • либо убрать параметр not null
  • либо указать значение по умолчанию
  • либо вставить значение
• если произойдет попытка вставки в поле с типом identity (автоинкремент), то также произойдет ошибка. Решить проблему можно двумя способами:
  • не вставлять значение в это поле
  • указать опцию identity_insert on после чего вставить уникальное значение для этого столбца

Demo Data

All demos in this article will use new objects we create here. This will allow us full reign to customize, test, and break it independently of anything else we are working on.

The following is the TSQL to create a table called dbo.accounts:

This is a fairly simple table with an identity ID and some string/date columns for account data. As we work through this article, we will add and remove columns, as well as customize this further.

Insert Data SQL Server via Stored Procedure or Dynamic SQL

Stored procedures are often used to return data to applications, reports, or additional TSQL scripts for processing. The results of any stored procedure can be inserted directly into a predefined table like this:

This monitoring script will execute sp_who2 to return a list of current processes in a given database. By default, this stored procedure returns all sessions, though parameters can be supplied to filter by login or session ID. To filter by database, though, would otherwise require returning all data and then manually removing the irrelevant rows. By creating a temporary table up front and inserting the results directly into it, we are then freed up to filter the result set by whatever criteria we wish. Any table may be used for this purpose, including permanent tables, as well as table variables.

The same syntax can be used to insert the results of dynamic SQL into a table, like this:

The end result of this code is that we use sp_executesql to execute dynamic SQL, placing the results directly into a temporary table.

In both of these scenarios, we were able to execute procedural TSQL and insert the results directly into an existing table. SELECT INTO is not supported with this syntax, but everything else we have discussed so far does work with this convention.

Get Inserted ID

For tables with an auto increment id field, you can get the id of the row you
just inserted by asking the result object.

Note: To be able to get the inserted id, only one row can be inserted.

Example

Insert a record in the «customers» table, and return the ID:

var mysql = require(‘mysql’);var con = mysql.createConnection({ 
host: «localhost»,  user: «yourusername»,  password: «yourpassword»,
  database: «mydb»
});con.connect(function(err) {  if (err) throw err;  var sql = «INSERT INTO customers (name, address)
VALUES (‘Michelle’, ‘Blue Village 1’)»; 
con.query(sql, function (err, result) {    if (err) throw err;    console.log(«1 record inserted,
ID: » + result.insertId);  });});

Save the code above in a file called «demo_db_insert_id.js», and run the file:

Run «demo_db_insert_id.js»

C:\Users\Your Name>node demo_db_insert_id.js

Which will give you something like this in return:

1 record inserted, ID: 15

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the «Customers» table:

And a selection from the «Suppliers» table:

Example — Using INSERT Statement to Insert Multiple Records

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly. Let’s look at an example of how to do this.

In this example, we have a table called employees with the following data:

And a table called customers with the following data:

Now, let’s insert some of the employee information into the customers table:

Try It

INSERT INTO customers
(customer_id, last_name, first_name)
SELECT employee_number AS customer_id, last_name, first_name
FROM employees
WHERE employee_number 
  
TIP: With this type of INSERT, some databases require you to alias the column names in the SELECT to match the column names of the table you are inserting into. As you can see in the example above, we have aliased the first column in the SELECT statement to customer_id.
  

There will be 2 records inserted. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

In this example, the last 2 records in the customers table have been inserted using data from the employees table.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running a COUNT(*) on the SELECT statement before performing the insert. For example:

Try It

SELECT COUNT(*)
FROM employees
WHERE employee_number 
  

This will return number of records that will be inserted when you execute the INSERT statement.

Conclusion

There are many ways to insert data into SQL Server, but not all were created equal. Picking the right syntax can have a significant impact on performance, documentation, and maintainability. This article provides a comparison of a variety of syntaxes, as well as the pros, cons, and demos of each one.

Always consider your application when writing code and adjust your style based on how often schema and code are expected to change. Schema and applications that are static and rarely changed can afford to have hard-coded INSERT statements with columns, aliases, and/or reference documentation to make them easy to read and maintain.

For schema or business needs that changes often, SELECT INTO or the omission of the column list from an INSERT may allow for more fluidity in places where code would greatly benefit from it.

Having a variety of options at our disposal allows us to make better decisions when smart choices are needed and will help us write better code that will make future developers appreciate us that much more as it is updated and maintained!

Example

The following script can be used to dump data into our FruitShop database. We use two statements — one for each table we want to populate. The first populates the Units table, the second populates the Fruit table.

Execute the following SQL script against the FruitShop database:

INSERT INTO Units
VALUES
(1,’Piece’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(2,’Kilogram’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(3,’Gram’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(4,’Pound’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(5,’Ounce’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(6,’Bunch’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(7,’Container’,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′);

INSERT INTO Fruit
VALUES
(1,’Apple’,10,1,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(2,’Orange’,5,2,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(3,’Banana’,20,6,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(4,’Watermelon’,10,1,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(5,’Grapes’,15,6,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′),
(6,’Strawberry’,12,7,’2015-02-15 10:30:00′,’2015-02-15 10:30:00′);

Insert Data into SQL Server Without an Explicit Column List

This leads us into the alternative way to insert data into an existing table, which is to do so without a column list. An insert of this form will look like this:

This statement executes successfully, despite no column list being provided. When SQL Server binds this query to its underlying objects, it will pull the column list in order and apply it to the data being inserted. If everything matches up, then we’re good to go, otherwise we will receive an error similar to the two we saw earlier that indicate column lists that do not match up.

The upside (if we wish to call it an upside) of this approach is that it is fast and requires less upkeep as there’s no need to write and maintain a list of columns with the insert. The downside is that if you mismatch columns, you may get a different sort of error message. Consider the following query:

When executed, we get a new error message:

In this scenario, we left out a column, but since this table includes NULLable columns, SQL Server tried to match up the table’s columns anyway using the data we provided, but was unable to make a meaningful match.

Because of the potential for confusing errors and the inability to easily match up columns to data, inserting into a table without providing a column list is not seen as a best practice and should be avoided whenever possible. I would recommend including a column list in all INSERT statements unless you happen to have a very unique use-case in which the convenience of the simpler syntax outweighs the risk of future code breaking if the table’s schema changes.

In addition, the column list provides clear documentation to the user as to what is being inserted and which columns the data will be going into. We can improve upon this further if we wish by adding aliases to all inserted columns:

Adding column aliases may seem like overkill, but when working with longer INSERT statements, dynamic SQL, or complex queries, the added documentation can greatly aid in writing, modifying, and troubleshooting those queries. You can also test your insert statement by highlighting the SELECT list only and executing it, which returns values along with the handy column headers:

Alternatively, you can provide that documentation in comments:

This can be a nice way to document without adjusting the query. The only downside is that you lose the handy column names that were demonstrated above with the test SELECT. Both are worth the extra 30 seconds to include in your code as they will save far more time in the future when the time comes to modify or troubleshoot that code.

Which Method is Best?

With a variety of methods to insert data into SQL Server available, the first question we will ask is which syntax should we use? The answer is going to depend on your use-case and specifically what is most important to a given application. To summarize our work so far:

Use an INSERT with an explicit column list for applications where column lists, inputs, and outputs do not change often. These are scenarios where change typically consist of column additions or alterations resulting from software releases. The column lists also add a layer of protection against logical errors if a column is added, removed, or altered without the INSERT statement also being updated. An error being thrown is a far better outcome than data quietly being handled incorrectly. This syntax is generally considered a best practice as it provides both documentation and protection against inadvertent mistakes should the schema change in the future.

An INSERT with no column list carves out a niche in scenarios where the columns are unknown or frequently changing. This might be useful in ETL, reporting, or scenarios involving transient data when the data is unstructured. Despite that possible application, I would lean towards using SELECT INTO for these applications as they provide a bit more safety against inadvertent mistakes with your data. An alternative to cover the need to insert into a pre-existing table would be to use SELECT INTO to create a temporary data structure and then insert from that temporary data into the permanent table using a formal column list.

SELECT INTO provides us the ability to quickly create a new table and dump data into it with very little setup or coding. For small data sets or those that are going to be scanned in their entirety, this is a great way to move data with minimal TSQL and maintenance. The primary downside is the inability to include indexes or constraints on the table until after it is created. This syntax must create a new table, which lends itself towards using temporary tables as a target to insert data to.

OUTPUT INSERTED allows us to return data from before, after, or before and after a DML statement. This is a hugely useful syntax and one that has no intrinsic downsides. It is a great alternative to iteration, functions, or triggers that may attempt to accomplish the same tasks. The syntax is relatively simple and can be applied to any data elements prior to or after a given change. You may even include data that is not directly affected by a DML statement, but was joined for use in the query!

Summary

There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.

In this article we will explore the different ways we can create and insert data into both permanent and temporary objects. Performance, syntax, documentation, and maintainability will be evaluated for each method. By delving deeper into this topic, we can improve database design, script quality, and build objects that are easier to upkeep and less likely to break due to maintenance or software releases.

Example — Using INSERT Statement to Insert One Record

The simplest way use the INSERT statement is to insert one record into a table using the VALUES keyword. Let’s look at an example of how to do this in SQL.

In this example, we have a table called categories with the following data:

Let’s insert a new category record. Enter the following SQL statement:

Try It

INSERT INTO categories
(category_id, category_name)
VALUES
(150, 'Miscellaneous');

There will be 1 record inserted. Select the data from the categories table again:

SELECT * FROM categories;

These are the results that you should see:

This example would insert one record into the categories table. This new record would have a category_id of 150 and a category_name of ‘Miscellaneous’.

In this example, because you are providing values for all of the columns in the categories table, you could omit the column names and instead write the INSERT statement like this:

Try It

INSERT INTO categories
VALUES
(150, 'Miscellaneous');