Insert into. ввод данных в таблицу базы данных в mysql
Содержание:
- MySQL INSERT INTO SELECT Examples
- Insert Multiple Records
- SQL Учебник
- Insert one row into a table
- SQL Справочник
- Insert Data SQL Server with OUTPUT INSERTED
- INSERT Statement Without Columns
- INSERT INTO Example
- Frequently Asked Questions
- Which Method is Best?
- Дополнительное использование
- Использование SELECT в инструкции INSERT INTO
- Get Inserted ID
- INSERT…EXECUTE statement
- The Basic INSERT Statement
MySQL INSERT INTO SELECT Examples
The following SQL statement copies «Suppliers» into «Customers» (the columns
that are not filled with data, will contain NULL):
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
The following SQL statement copies «Suppliers» into «Customers» (fill all
columns):
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)SELECT SupplierName, ContactName, Address, City, PostalCode,
Country FROM Suppliers;
The following SQL statement copies only the German suppliers into «Customers»:
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM SuppliersWHERE Country=’Germany’;
❮ Previous
Next ❯
Insert Multiple Records
To insert more than one record, make an array containing the values, and
insert a question mark in the sql, which will be replaced by the value array:
Example
Fill the «customers» table with data:
var mysql = require(‘mysql’);var con = mysql.createConnection({
host: «localhost», user: «yourusername»,
password: «yourpassword», database: «mydb»});
con.connect(function(err) { if (err) throw err;
console.log(«Connected!»); var sql = «INSERT INTO customers (name,
address) VALUES ?»; var values = , ,
, ,
, ,
, ,
, ,
, ,
,
]; con.query(sql, , function (err, result)
{ if (err) throw err; console.log(«Number
of records inserted: » + result.affectedRows); });});
Save the code above in a file called «demo_db_insert_multple.js», and run the file:
Run «demo_db_insert_multiple.js»
C:\Users\Your Name>node demo_db_insert_multiple.js
Which will give you this result:
Connected!Number of records inserted: 14
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Insert one row into a table
To insert one row into a table, you use the following syntax of the statement.
There are some points that you should pay attention to when you insert a new row into a table:
- First, the number of values must be the same as the number of columns. In addition, the columns and values must be the correspondent because the database system will match them by their relative positions in the lists.
- Second, before adding a new row, the database system checks for all integrity constraints e.g., foreign key constraint, primary key constraint, check constraint and not null constraint. If one of these constraints is violated, the database system will issue an error and terminate the statement without inserting any new row into the table.
It is not necessary to specify the columns if the sequence of values matches the order of the columns in the table. See the following statement that omits the column list in the clause.
However, this is not considering as a good practice.
If you don’t specify a column and its value in the statement when you insert a new row, that column will take a default value specified in the table structure. The default value could be 0, a next integer value in a sequence, the current time, a NULL value, etc. See the following statement:
In this syntax, the will take a default value.
Insert one row into a table example
We will use the and tables in the sample database to show you how to insert one row into the table.
To insert a new row into the table.
We did not use the column in the statement because the column is an auto-increment column, therefore, the database system uses the next integer number as the default value when you insert a new row.
The column is a foreign key that links the table to the table. Before adding the new rows, the database system checks if the value 178 exists in the column of the table to make sure that the foreign key constraint is not violated.
If the row is inserted successfully, the database system returned the number of the affected rows.
You can check whether the row has been inserted successfully or not by using the following SELECT statement.
SQL Справочник
SQL Ключевые слова
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 Функции
Функции строк
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
Функции чисел
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
Функции дат
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
Функции расширений
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 функции
Функции строк
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
Функции чисел
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
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
Insert Data SQL Server with OUTPUT INSERTED
A bonus way we can generate data is via an existing statement. When we execute any write operation, we may output data from before or after the change to another table. Here is an example of how this looks:
|
1 |
CREATETABLE#account_ids (account_idINTNOTNULLPRIMARYKEYCLUSTERED); UPDATEaccount SETis_active=1 OUTPUTINSERTED.account_id INTO#account_ids FROMdbo.account WHEREaccount_type=’LIVE’; SELECT*FROM#account_ids; DROPTABLE#account_ids; |
The goal above is to update all accounts of type “LIVE” to be active. We also want to return the account_id for each account that was updated. Using OUTPUT INSERTED allows us to accomplish both tasks in a single set-based solution. The results show us which IDs were affected by the update statement:
INSERTED will contain all columns in the table as they appear after changes have been applied. Similarly, DELETED will contain the previous versions. We can mix and match these for maximum effect:
|
1 |
CREATETABLE#account_ids (account_idINTNOTNULLPRIMARYKEYCLUSTERED,is_active_previousBITNOTNULL,is_active_currentBITNOTNULL); UPDATEaccount SETis_active= OUTPUTINSERTED.account_id, DELETED.is_active, INSERTED.is_active INTO#account_ids FROMdbo.account WHEREaccount_type=’LIVE’; SELECT*FROM#account_ids; DROPTABLE#account_ids; |
The results show that we not only captured the account IDs, but also the previous and new values for the is_active flag. This is a huge convenience as we can use OUTPUT INSERTED in INSERT, DELETE, UPDATE, and MERGE statements to quickly pull before & after data for use in additional processing.
This is a far superior solution to iteration and/or using SCOPE_IDENTITY() and is the only easy way to capture data in this fashion with so little code. OUTPUT INSERTED is a great method for getting before & after data from DML statements. It is also useful for collecting a list of rows that were altered in a given TSQL statement, so we can take additional actions or report on them as needed.
INSERT Statement Without Columns
What happens if you have an SQL INSERT INTO statement without specifying the columns?
Earlier I mentioned that they were optional, so what happens?
1 row inserted.
| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_REQUIRED | FEES_PAID | ENROLMENT_DATE | GENDER |
| 1 | John | Smith | 500 | 100 | 01/Feb/15 | M |
| 2 | Susan | Johnson | 150 | 150 | 12/Jan/15 | F |
| 3 | Tom | Capper | 350 | 320 | 06/Mar/15 | M |
| 4 | Mark | Holloway | 500 | 410 | 20/Jan/15 | M |
| 5 | Steven | Webber | 100 | 80 | 09/Mar/15 | M |
| 6 | Julie | Armstrong | 100 | 12/Feb/15 | F | |
| 7 | Michelle | Randall | 250 | 23/Jan/15 | F | |
| 8 | Andrew | Cooper | 800 | 400 | 04/Mar/15 | M |
| 9 | Robert | Pickering | 110 | 100 | 30/Jan/15 | M |
| 10 | Tanya | Hall | 150 | 150 | 28/Jan/15 | F |
| 11 | Jarrad | Winston | 700 | 300 | (null) | (null) |
| 12 | Mary | Taylor | 500 | 100 | (null) | F |
We can see that the record was inserted. However, we could have some problems with this:
- What if we alter the table and add or remove columns? The INSERT statement may not work anymore.
- What if we put the values in the wrong order in the INSERT statement? It will also fail.
So, while it could work, it’s generally not a good idea.
INSERT INTO Example
The following SQL statement inserts a new record in the «Customers» table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
The selection from the «Customers» table will now look like this:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 89 | White Clover Markets | Karl Jablonski | 305 — 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
| 90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
| 91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
| 92 | Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
Did you notice that we did not insert any number into the CustomerID
field?The CustomerID column is
an auto-increment field and will be
generated automatically when a new record is inserted into the table.
Frequently Asked Questions
Question: I am setting up a database with clients. I know that you use the Oracle INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following Oracle INSERT statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
FROM clients
WHERE clients.client_id = suppliers.supplier_id);
This Oracle INSERT statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following Oracle INSERT statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
FROM clients
WHERE clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Question: How can I insert multiple rows of explicit data in one INSERT command in Oracle?
Answer: The following is an example of how you might insert 3 rows into the suppliers table in Oracle, using an Oracle INSERT statement:
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!
Дополнительное использование
Совершенно по-другому применяется кнопка Insert в различных модификациях файловых менеджеров. К последним можно отнести FAR, DOS Navigator и Norton Commander. Конечно, сейчас такое программное обеспечение крайне редко используется, но знать некоторые его особенности будет не лишним. Если курсор находится на одной из панелей такого софта, то нажатие этой кнопки на подсистеме ввода приведет к тому, что будет выделен тот элемент, на котором находится маркер. При этом указатель сместится на одну позицию вниз. Если же повторно нажать эту клавишу на ранее выделенном элементе, отменится ранее выполненное действие.
В рамках же операционной системы клавиша Insert может использоваться в различных комбинациях клавиш. Например, если зажать сначала Ctrl, а потом Insert, и после этого одновременно их отпустить, то выделенный фрагмент документа или же файл будет помещен в буфер обмена. С помощью другой комбинации можно выполнить обратную операцию. То есть извлечь информацию из буфера обмена и установить ее в место нахождения курсора. В этом случае зажимаем сразу Shift, потом Insert и после этого их отпускаем.
Использование SELECT в инструкции INSERT INTO
Можно использовать инструкцию MySQL INSERT SELECT для копирования строк из одной таблицы и их вставки в другую.
Использование этого оператора аналогично использованию INSERT INTO. Разница в том, что оператор SELECT применяется для выборки данных из другой таблицы. Ниже приведены различные способы использования INSERT INTO SELECT:
Вставка всех столбцов таблицы: можно скопировать все данные таблицы и вставить их в другую таблицу.
Синтаксис:
INSERT INTO первая_таблица SELECT * FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы.
Мы использовали инструкцию SELECT для копирования данных из одной таблицы и инструкцию INSERT INTO для их вставки в другую.
Вставка отдельных столбцов таблицы. Можно скопировать только те столбцы таблицы, которые необходимо вставить в другую таблицу.
Синтаксис:
INSERT INTO первая_таблица(имена_столбцов1) SELECT имена_столбцов2 FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы. имена_столбцов1: имена столбцов, разделенные запятой(,) для таблицы 1. имена_столбцов2: имена столбцов, разделенные запятой(,) для таблицы 2.
Мы использовали инструкцию SELECT для копирования данных только из выбранных столбцов второй таблицы и инструкцию INSERT INTO MySQL SELECT для их вставки в первую таблицу.
Копирование определенных строк из таблицы. Можно скопировать определенные строки из таблицы для последующей вставки в другую таблицу с помощью условия WHERE с оператором SELECT. В этом случае нужно использовать соответствующее условие в WHERE.
Синтаксис:
INSERT INTO таблица1 SELECT * FROM таблица2 WHERE условие; таблица1: имя первой таблицы. таблица2: имя второй таблицы. условие: условие для выбора строк.
Таблица 2: LateralStudent
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Запросы:
Способ 1 (вставка всех строк и столбцов):
INSERT INTO Student SELECT * FROM LateralStudent;
Результат:
Этот запрос вставит все данные таблицы LateralStudent в таблицу Student. После применения INSERT INTO SQL SELECT таблица Student будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Способ 2 (вставка отдельных столбцов):
INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Результат:
Этот запрос вставит данные из столбцов ROLL_NO, NAME и Age таблицы LateralStudent в таблицу Student. Для остальных столбцов таблицы Student будет задано значение null. После применения SQL INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | Null | null | 18 |
| 8 | NIRAJ | Null | null | 19 |
| 9 | SOMESH | Null | null | 20 |
Выбор определенных строк для вставки:
INSERT INTO Student SELECT * FROM LateralStudent WHERE Age = 18;
Результат:
Этот запрос выберет только первую строку из таблицы LateralStudent для вставки в таблицу Student. После применения INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
Пожалуйста, оставляйте ваши мнения по текущей теме статьи. За комментарии, дизлайки, подписки, лайки, отклики огромное вам спасибо!
Пожалуйста, оставляйте ваши комментарии по текущей теме материала. Мы крайне благодарны вам за ваши комментарии, отклики, подписки, дизлайки, лайки!
Вадим Дворниковавтор-переводчик статьи «SQL INSERT INTO Statement»
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
❮ Previous
Next ❯
INSERT…EXECUTE statement
The second INSERT… EXECUTE statement, the stored procedure is executed and that contains the SELECT statement. The following example, the tb_spaceused table is created.
|
1 |
CREATETABLEtb_spaceused (database_nameNVARCHAR(128), database_sizeVARCHAR(18), unallocatedspaceVARCHAR(18), reservedVARCHAR(18), dataVARCHAR(18), index_sizeVARCHAR(18), unusedVARCHAR(18) ); |
The INSERT INTO SQL statement uses the EXECUTE clause to invoke a stored procedure that contains the result set of the SELECT statement.
|
1 |
INSERTINTOtb_spaceused EXECsp_msforeachdb @command1=»use?execsp_spaceused@oneresultset=1″; SELECT* FROMtb_spaceused; |
The Basic INSERT Statement
Let’s start by looking at a basic INSERT statement.
Assume we have a table that looks like this:
| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_REQUIRED | FEES_PAID | ENROLMENT_DATE | GENDER |
| 1 | John | Smith | 500 | 100 | 01/Feb/15 | M |
| 2 | Susan | Johnson | 150 | 150 | 12/Jan/15 | F |
| 3 | Tom | Capper | 350 | 320 | 06/Mar/15 | M |
| 4 | Mark | Holloway | 500 | 410 | 20/Jan/15 | M |
| 5 | Steven | Webber | 100 | 80 | 09/Mar/15 | M |
| 6 | Julie | Armstrong | 100 | 12/Feb/15 | F | |
| 7 | Michelle | Randall | 250 | 23/Jan/15 | F | |
| 8 | Andrew | Cooper | 800 | 400 | 04/Mar/15 | M |
| 9 | Robert | Pickering | 110 | 100 | 30/Jan/15 | M |
| 10 | Tanya | Hall | 150 | 150 | 28/Jan/15 | F |
We can use the INSERT statement to insert a new record into the table.
To use the INSERT statement in SQL, we need a few things:
- The name of the table we want to insert data into
- The values to insert into the table
- The columns to insert the values into (this is actually optional)
We don’t need the names of the columns, but it’s good practice to specify them. If we don’t, then there’s a risk that if the table structure changes, the INSERT statements will fail.
Let’s take a look at an INSERT statement for this table.
We start with the INSERT INTO, which are SQL keywords.
Then, we mention the table. In this case, it’s student.
Then, we open the brackets, and inside the brackets, we have a list of columns. These are each of the columns that we want to insert values in to. We don’t need to specify all of the columns (I’ll explain this more shortly), but in this case, I have.
Next comes the keyword VALUES.
Then, we open the brackets and specify the values that we want to insert. Inside this list is a comma-separated list of values. These values align with the columns we specified earlier.
The first value matches the first column, the second value matches the second column, and so on.
The values can be specified as:
- Numbers, which don’t need to be inside quotes.
- Strings, which need to be inside single quotes
- NULL, which means a NULL value will be inserted (which we have done with this example for the enrolment_date column)
- Functions or other columns, which we will get to later.
Finally, we close the brackets and end with a semicolon.
What will this query do? Let’s run it and find out.
1 row inserted.
| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_REQUIRED | FEES_PAID | ENROLMENT_DATE | GENDER |
| 1 | John | Smith | 500 | 100 | 01/Feb/15 | M |
| 2 | Susan | Johnson | 150 | 150 | 12/Jan/15 | F |
| 3 | Tom | Capper | 350 | 320 | 06/Mar/15 | M |
| 4 | Mark | Holloway | 500 | 410 | 20/Jan/15 | M |
| 5 | Steven | Webber | 100 | 80 | 09/Mar/15 | M |
| 6 | Julie | Armstrong | 100 | 12/Feb/15 | F | |
| 7 | Michelle | Randall | 250 | 23/Jan/15 | F | |
| 8 | Andrew | Cooper | 800 | 400 | 04/Mar/15 | M |
| 9 | Robert | Pickering | 110 | 100 | 30/Jan/15 | M |
| 10 | Tanya | Hall | 150 | 150 | 28/Jan/15 | F |
| 11 | Jarrad | Winston | 700 | 300 | (null) | (null) |
As you can see, a single record has been added to this table.
