INSERT

INSERT STATEMENT:

Data modification language (DML) statement available in Transact-SQL. Insert statement provide the ability to add one or more rows 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.

SQL offers six forms of INSERT and SELECT/INTO as the primary methods of inserting data (as shown in Table 15-1). The most basic method simply inserts a row of data, while the most complex builds a data set from a complex SELECT statement and creates a table from the result.

Each of these INSERT forms is useful for a unique task, often depending on the source of the data being inserted.

Insert Form                                                                                                                                                                                                                                                                                                        Description

INSERT/VALUES                                                                                                                                                                                                Inserts one or more rows of values; commonly used to insert

data from a user interface

INSERT/SELECT                                                                                                                                                                                                  Inserts a result set; commonly used to manipulate sets of data

INSERT/EXEC                                                                                                                                                                                                      Inserts the results of a stored procedure; used for complex data

Manipulation

INSERT/DEFAULT VALUES                                                                                                                                            Creates a new row with all defaults; used for pre-populating

pigeonhole data rows

SELECT/INTO                                                                                                                                                                                                      Creates a new table from the result set of a SELECT statement

MERGE                                                                                                                                                                                                                                                 Combines inserting, updating, and deleting data in a single

Statement

Inserting simple rows of values: INSERT/VALUES:

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

The simplest and most direct method of inserting data is the INSERT/VALUES method. Until SQL Server 2008, INSERT. . .VALUES was limited to inserting a single row, but SQL Server is now compliant with the ANSI standard and can include row constructors — inserting multiple rows in a single INSERT. . .VALUES statement:

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)

The INTO keyword is optional and is commonly ignored.

The key to building an INSERT statement is getting the columns listed correctly and ensuring that the data type of the value is valid for the inserted column.

When a column has both no default and a NOT NULL constraint, and no value is provided in the INSERT statement, the INSERT operation will fail.

Data Insertion into a table has a default constraint.

It’s possible to explicitly force the INSERT of a default without knowing the default value. If the keyword DEFAULT is provided in the value-column list, then SQL Server will store the default value for the column. This is a good practice because it documents the intention of the code, rather than leaving the code blank and assuming the default.

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')

Creating a default row:

SQL includes a special form of the INSERT command that creates a single new row with only default values. The only parameter of the new row is the table name. Data and column names are not required.

The syntax is very simple, as shown here:

INSERT schema.Table DEFAULT VALUES;

I have never used this form of INSERT in any real-world applications. It could be used to create ‘‘pigeon hole’’ rows with only keys and null values, but I don’t recommend that design.

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.

When the data to be inserted, usually in the form of variables sent from the user interface, is known, inserting using the INSERT. . .VALUES form is the best insert method.

Typically, to reference values from a data source, the INSERT. . .SELECT is used, but an INSERT. . .VALUES can include a scalar subquery as one of the values.

Retrieving Data from Other Tables: INSERT/SELECT

You can instead retrieve the values through a SELECT statement.

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>

Inserting the result set from a stored procedure:

The INSERT. . .EXEC form of the INSERT operation pulls data from a stored procedure and inserts it into a table. Behind these inserts are the full capabilities of T-SQL. The basic function is the same as that of the other insert forms. The columns have to line up between the INSERT columns and the storedprocedure result set.

Here’s the basic syntax of the INSERT. . .EXEC command:

INSERT [INTO] schema.Table [(Columns)]

EXEC StoredProcedure Parameters;

Be careful, though, because stored procedures can easily return multiple record sets, in which case the INSERT attempts to pull data from each of the result sets, and the columns from every result set must line up with the insert columns.

The following code sample builds a stored procedure that returns the first and last names of all guides from both the Cape Hatteras Adventures database and Microsoft’s Northwind sample database from SQL Server 2000. Next, the code creates a table as a place to insert the result sets. Once the stored procedure and the receiving table are in place, the sample code performs the INSERT. . .EXEC statement:

Use CHA2;

CREATE PROC ListGuides

AS

SET NOCOUNT ON;

-- result set 1

SELECT FirstName, LastName

FROM dbo.Guide;

-- result set 1

SELECT FirstName, LastName

FROM Northwind.dbo.Employees;

RETURN;

When the List Guides stored procedure is executed, two result sets should be produced:

Exec ListGuides;

Result:

FirstName LastName

----------------------- ------------------------

Dan Smith

Jeff Davis

Tammie Commer

Lauren Jones

Greg Wilson

FirstName LastName

---------- --------------------

Nancy Davolio

Andrew Fuller

Janet Leverling

Margaret Peacock

Steven Buchanan

Michael Suyama

Robert King

Laura Callahan

Anne Dodsworth

The following DDL command creates a table that matches the structure of the procedure’s result sets:

CREATE TABLE dbo.GuideSample

(FirstName VARCHAR (50),

LastName VARCHAR (50),

CONSTRAINT PK_GuideSample PRIMARY KEY (FirstName, LastName) );

With the situation properly set up, here’s the INSERT. . .EXEC command:

INSERT dbo.GuideSample (FirstName, LastName)

Exec ListGuides;

A SELECT command can read the data and verify that fourteen rows were inserted:

SELECT FirstName, LastName

FROM dbo.GuideSample;

Result:

FirstName LastName

-------------------- --------------------

Dan Smith

Jeff Davis

Tammie Commer

Lauren Jones

Wilson Greg

Nancy Davolio

Andrew Fuller

Janet Leverling

Margaret Peacock

Steven Buchanan

Michael Suyama

Robert King

Laura Callahan

Anne Dodsworth

INSERT/EXEC does require more work than INSERT/VALUES or INSERT/SELECT, but because the stored procedure can contain complex logic, it’s the most powerful of the three.

Creating a table while inserting data

The last method of inserting data is a variation on the SELECT command. The INTO select option takes the results of a SELECT statement and creates a new table containing the results. SELECT. . .INTO is often used during data conversions and within utilities that must dynamically work with a variety of source-table structures. The full syntax includes every SELECT option.

Here’s an abbreviated syntax to highlight the function of the INTO option:

SELECT Columns

INTO NewTable

FROM DataSources

[WHERE conditions];

The data structure of the newly created table might be less of an exact replication of the original table structure than expected because the new table structure is based on a combination of the original table and the result set of the SELECT statement. String lengths and numerical digit lengths may change. If the SELECT. . .INTO command is pulling data from only one table and the SELECT statement contains no data-type conversion functions, then there’s a good chance that the table columns and null settings will remain intact. However, keys, constraints, and indexes will be lost.

SELECT. . .INTO is a bulk-logged operation, similar to BULK INSERT and BULK COPY. Bulk-logged operations may enable SQL Server to quickly move data into tables by minimally recording the bulk-logged operations to the transaction log (depending on the database’s recovery model). Therefore, the database options and recovery model affect SELECT. . .INTO and the other bulk-logged operations.

SELECT/INTO can serve many useful functions:

• If zero rows are selected from a table, then SELECT/INTO will create a new table with only the data schema (though with the limitations listed earlier).

• If SELECT reorders the columns, or includes the cast() function, then the new table will retain the data within a modified data schema.

• When combined with a UNION query, SELECT/INTO can combine data from multiple tables vertically. The INTO goes in the first SELECT statement of a UNION query.

• SELECT/INTO is especially useful for denormalizing tables. The SELECT statement can pull from multiple tables and create a new flat-file table.

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