How To Insert Data Into Sql Table

totn SQL Server

SQL Server: INSERT Argument

This SQL Server tutorial explains how to utilise the INSERT statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) INSERT statement is used to insert a unmarried tape or multiple records into a table in SQL Server.

Syntax

In its simplest form, the syntax for the INSERT argument when inserting a unmarried record using the VALUES keyword in SQL Server (Transact-SQL) is:

INSERT INTO table (column1, column2, ... ) VALUES (expression1, expression2, ... ), (expression1, expression2, ... ), ...;

However, the total syntax for the INSERT statement when inserting a single record using the VALUES keyword in SQL Server (Transact-SQL) is:

INSERT INTO table (column1, column2, ... ) VALUES ( DEFAULT | Nix | expression1,    DEFAULT | NULL | expression2,    ... );

Or...

The syntax for the SQL Server INSERT statement when inserting a single record using the DEFAULT VALUES keyword is:

INSERT INTO table (column1, column2, ... ) DEFAULT VALUES;

Or...

In its simplest class, the syntax for the SQL Server INSERT statement when inserting multiple records using a sub-select is:

INSERT INTO tabular array (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table [WHERE weather];

All the same, the total syntax for the SQL Server INSERT statement when inserting multiple records using a sub-select is:

INSERT [ Acme (top_value) [ Percent ] ] INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table [WHERE conditions];

Parameters or Arguments

tabular array: The table to insert the records into.
column1, column2: The columns in the table to insert values.
expression1, expression2: The values to assign to the columns in the table. And then column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and and so on.
Height (top_value): Optional. If specified, it volition insert the tiptop number of rows based on top_value. For example, Height(10) would insert the meridian ten rows from the full result set.
Percentage: Optional. If Pct is specified, then the top rows are based on a top_value percentage of the total upshot prepare (as specfied past the PERCENT value). For case, Superlative(10) Percentage would insert the peak 10% of the full consequence set.
source_table: The source table when inserting data from another table.
WHERE conditions: Optional. The conditions that must exist met for the records to exist inserted.

Note

When inserting records into a table using the SQL Server INSERT statement, yous must provide a value for every NOT Aught column.
You can omit a column from the SQL Server INSERT statement if the column allows Nothing values.

Case - Using VALUES keyword

The simplest way to create a SQL Server INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO employees (employee_id, last_name, first_name) VALUES (10, 'Anderson', 'Sarah');

This SQL Server INSERT argument would upshot in one record existence inserted into the employees table. This new record would take an employee_id of ten, a last_name of 'Anderson', and a first_name of 'Sarah'.

You can besides use this syntax to insert more than than one record at a time. For example:

INSERT INTO employees (employee_id, last_name, first_name) VALUES (10, 'Anderson', 'Sarah'), (11, 'Johnson', 'Dale');

This INSERT instance shows how to insert more than ane tape using the VALUES keyword. In this example, 2 records are inserted into the employees table. The starting time record has an employee_id of 10, a last_name of 'Anderson', and a first_name of 'Sarah'. The 2nd record has an employee_id of 11, a last_name of 'Johnson', and a first_name of 'Dale'.

This would be equivalent to the post-obit two INSERT statements:

INSERT INTO employees (employee_id, last_name, first_name) VALUES (x, 'Anderson', 'Sarah');  INSERT INTO employees (employee_id, last_name, first_name) VALUES (11, 'Johnson', 'Dale');

Instance - Using DEFAULT VALUES keyword

In SQL Server, y'all can also insert a record into a table using the DEFAULT VALUES syntax.

For example:

INSERT INTO employees (employee_id, last_name, first_name) DEFAULT VALUES;

This SQL Server INSERT statement would result in ane record being inserted into the employees table. This new tape would be created with default values for the employee_id, last_name, and first_name fields.

Example - Using SELECT statement

You tin can also create more complicated SQL Server INSERT statements using SELECT statements.

For example:

INSERT INTO contacts (contact_id, last_name, first_name) SELECT employee_id, last_name, first_name FROM employees WHERE employee_id <= 100;

Past placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you lot may wish to check for the number of rows existence inserted. Y'all can determine the number of rows that will be inserted by running the following SQL Server SELECT statement before performing the insert.

SELECT count(*) FROM employees WHERE employee_id <= 100;