This tutorial will explain how to insert data into a table in SQL and how to use them with the examples.
The following statement will insert a row into a table
INSERT INTO tablename
(columnname1, columnname2, columnname3,...)
VALUES
(columnvalue1, columnvalue2, columnvalue3, ...);
Lets create a sample table to use it in our examples
CREATE TABLE Table1 (
CharColumn VARCHAR(50) NOT NULL,
INTColumn INT NULL,
TINYINTColumn TINYINT DEFAULT 1 NOT NULL
);
Given number of columnnames and values should be equal;
The following sample will fail as there is a missing value for INTColumn
INSERT INTO Table1
(CharColumn, INTColumn)
VALUES
('Test1');
Lets correct the number of column and values. So removed INTColumn from columnnames. The missing columns within the statement will be entered with their default value, if no default value defined than it will be entered as NULL.
INSERT INTO Table1
(CharColumn)
VALUES
('Test1');
Table records will be as below. As INTColumn has no default value, it has been entered as NULL value. As TINYINTColumn has 1 as default value, it has been entered as 1.
| CharColumn | INTColumn | TINYINTColumn |
--------------------------------------
| Test1 | NULL | 1 |
* Given value format should match column data type
The following sample will fail as there is a missmatch value for INTColumn, it should be an integer value.
INSERT INTO Table1
(CharColumn, INTColumn)
VALUES
('Test2', 'Test2');
The correct format should be as following
INSERT INTO Table1
(CharColumn, INTColumn)
VALUES
('Test2', 2);
The following sample will fail as CharColumn does not accept NULL value.
INSERT INTO Table1
(CharColumn, INTColumn)
VALUES
(NULL, 3);
The correct format should be as following (not a null value)
INSERT INTO Table1
(CharColumn, INTColumn)
VALUES
('Test3', 3);
The following sample will fail as TINYINTColumn does not accept any value greater than 255.
INSERT INTO Table1
(CharColumn, INTColumn, TINYINTColumn)
VALUES
('Test4', 4, 256);
The correct format should be as following
INSERT INTO Table1
(CharColumn, INTColumn, TINYINTColumn)
VALUES
('Test4', 4, 255);
You can comma seperation to insert multiple rows;
INSERT INTO Table1
(CharColumn, INTColumn, TINYINTColumn)
VALUES
('Test5', 5, 250),
('Test6', 6, 251),
('Test7', 7, 22)
Lets create another table to demonstrate inserting into a table from another table with a select statement. We create with the same two fields but INTCOLUMN will not accept NULL value.
CREATE TABLE Table2 (
CharColumn VARCHAR(50) NOT NULL,
INTColumn INT NOT NULL,
);
Here is the syntax to insert into a table with a select statement
INSERT INTO table1 (column1, column2)
SELECT
column1,
column2
FROM
table2
WHERE
condition1;
Lets try to copy without any where clause, this will try to copy all records from Table1 into Table2. This will fail as INTColumn on Table1 contains NULL value but INTColumn on Table2 does not accept NULL value.
INSERT INTO Table2 (CharColumn, INTColumn)
SELECT CharColumn, INTColumn FROM Table1
We have two options
1) A where clause can be added to insert only not null records
INSERT INTO Table2 (CharColumn, INTColumn)
SELECT CharColumn, INTColumn FROM Table1 WHERE INTColumn IS NOT NULL
2) Default value can be placed in the select statement where it is NULL value
INSERT INTO Table2 (CharColumn, INTColumn)
SELECT CharColumn, (CASE
WHEN INTColumn IS NULL THEN 1
ELSE INTColumn
END) INTColumn
FROM Table1