Data modification language (DML) statement available in Transact-SQL.
Insert statement provide the ability to add a new row to a table.
When inserting data you can include all columns or partial number of columns.
Default constraint,Identity property,computed columns and Null settings may effect the data requirements of your INSERT statement.
Performing a Basic Insert:
When inserting data in all the columns then no need to specify all columns list instead, you can use Values clause as below.
Insert into <table name>
Values (val1, val2, val3)
Notice that the values are enclosed in parentheses and separated by commas.
In addition, string values are enclosed in single quotation marks.
The values are inserted into the table in the order they’re specified in the clause. That means the values must be in the same order as the columns are defined in the table.
Inserting multiple rows of data
you might want to add multiple rows to a table in a single statement.
Prior to SQL Server 2008, this was not possible, but now the INSERT statement let’s you specify multiple rows, as shown in the following example:
INSERT SalesStaff1 VALUES
(2, 'Michael', 'Blythe'),
(3, 'Linda', 'Mitchell'),
(4, 'Jillian', 'Carson
(5, 'Garrett', 'Vargas');
Inserting data that is not in the same order as the table columns:
When inserting data in few columns then should specify columns list in the INSERT statement.
Insert into <table name> (col1,col2,col3)
Values (val1, val2, val3)
· Data Insertion into a table has a default constraint.
create table test(Course varchar(30) default 'Data base',name varchar(30))
insert into test values(DEFAULT,'sql Server')
insert into test (name) values('sql Server')
insert into test (Course,name) values(DEFAULT,'sql Server')
insert into test (Course,name) values('Programing','C')
- Data Insertion into a table has a Computed column.
create table test(id int,
firstname varchar(20),
lastname varchar(20),
name as (firstname+' '+lastname)
)
insert into test values(10,'sql','server')
insert into test values(10,'sql','server','ms sql server')
If we try to insert data into computed column then it will throw an error message as below.
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
- Has an IDENTITY property. The next incremental identity value is used.
- Has a default. The default value for the column is used.
- Has a timestamp data type. The current timestamp value is used.
create table #test(id timestamp,name varchar(10))
insert into #test values (default,'sql')
insert into #test values ('sql')
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
- Is nullable. A null value is used.
- Is a computed column. The calculated value is used.
- Column defined with the rowversion data type, which automatically generates a unique, incrementing binary number.
INSERT INTO T1 DEFAULT VALUES;
All the columns in table has default values we can use above syntax
- The INTO keyword is optional in INSERT statement.
Insert <table name> (col1,col2,col3)
Values (val1, val2, val3)
Insert <table name>
Values (val1, val2, val3)
Retrieving Data from Other Tables:
You can instead retrieve the values through a SELECT statement or through a stored procedure.
A SELECT statement that’s used within an INSERT statement can reference a common table expression (CTE) as well as a table or view, as long as that CTE precedes the INSERT statement (as opposed to being part of the SELECT statement itself).
SELECT:
Using INSERT ... SELECT we can append rows to a table based on rows selected from different tables.
INSERT <targettable_name> (col1,col2)
SELECT col1,col2
FROM <table_name>
The total Number of rows and data type selected in <table_name> should match with the destination table <targettable_name>.
CTE:
WITH <ctetable_name> (col1,col2)
AS
(
SELECT col1,col2
FROM <table_name>
)
INSERT <targettable_name> (col1,col2)
SELECT col1,col2
FROM <ctetable_name>
EXEC:
INSERT <targettable_name> (col1,col2)
EXEC Proc_name
It will also allows to create new temporary or permanent table.
select * into #temp_test
from <table_name2>
select * into permanent_test
from <table_name2>
You can also include an OUTPUT clause in your INSERT statement to capture the statement’s results for auditing or verification purposes.
Diagram:
Sample data base objects creation:
create table test(id int ,firstname varchar(20),lastname varchar(20),name as (firstname+' '+lastname))
create table #temp_table (id int,name varchar(30))
create proc test_proc as
begin
select 13,'sybase'
end
Sample Data Insertion:
Insert into test values(10,'sql','server')
Select * from test
id firstname lastname name
10 sql server sql server
Standard INSERT query which include all the clauses(except rowset functions):
with cte(id,name) as
(
select ID,name from test
) --Common table expression
INSERT TOP (1) --We can also use TOP (50) PERCENT in place of TOP 1
INTO test --Object name
WITH (IGNORE_TRIGGERS) -–Table level hint
(ID,firstname) --Columns List
OUTPUT INSERTED.id,inserted.firstname
INTO #temp_table --capturing inserted rows into temporary table using
OUTPUT caluse
select ID,firstname from test --data from external table
We can also use below statements instead of external table
Exec test_proc --Execute Statement
Values(12,'oracle') --Values clause
select id,name from cte --Derived table
Related Topics:
SET IDENTITY_INSERT:
Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
Arguments
Database
Is the name of the database in which the specified table resides.
Owner
Is the name of the table owner.
Table
Is the name of a table with an identity column.
Remarks
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
Permissions
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.
Examples
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.
-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO
-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO
SELECT *
FROM products
GO
-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
No comments:
Post a Comment