Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 4. Data Modification with SQL Server Transact-SQL Statements

In the previous chapter, you learned how to query data stored in the SQL Server database tables. In this chapter, you will learn how to insert, update, and delete data in tables using Transact-SQL Data Manipulation Language (DML) statements. You will also learn how you can use Transact-SQL MERGE statements to perform multiple DML operations against a specified target table based on the results of join conditions with a source table. In the final section of this chapter, you will use Transact-SQL TRUNCATE TABLE statements to remove all data from a table, and use Transact-SQL SELECT INTO statements to create a table and (optionally) populate it with rows from a query.

In this chapter, we'll cover the following topics:

Inserting data into SQL Server database tables
Updating data in SQL Server database tables
Deleting data from SQL Server database tables
Using Transact-SQL MERGE statements to perform multiple DML operations using a single code block
Using TRUNCATE TABLE statements
Using SELECT INTO statements

Inserting data into SQL Server database tables

In SQL Server, you use the INSERT statement to add one new row or multiple new rows to an existing table. You can use the INSERT statement to insert data into a particular column, all columns, and IDENTITY columns.

To execute INSERT statements, a user must have at least the INSERT permission assigned on the target table.

The following is the basic syntax for INSERT statements:

[WITH <common_table_expression> [,...n]]
INSERT [TOP (expression) [PERCENT]
[INTO]
table_name | view_name | rowset function | common_table_expression
[WITH table_hints ]
<output_clause>
[(column_list)]
{VALUES (values_list) | select_statement | DEFAULT | execute_statement | dml_table_source
NULL
} | DEFAULT VALUES

The column_list parameter specifies a list of one or more columns in which you are inserting data. The column_list parameter is optional when you are providing a value for each column in the table, and the values appear in the exact order in which the columns are defined. If a column does not appear, then SQL Server must generate a value for that column. SQL Server can automatically provide values for IDENTITY columns, columns with the timestamp data type, columns with a default value, and nullable columns.

Note

Although the column_list parameters are optional for the SELECT and INSERT statements, it is always a good idea to provide a full-column list for these statements. This is because if the full-column list is not specified, SQL Server resolves full-column lists whenever the SELECT and INSERT statements execute. Moreover, the SELECT or INSERT statement may generate an error if the underlying table schema changes.

The other arguments of the INSERT statement are explained as follows:

WITH: This keyword specifies the common table expression (CTE) that you can reference in your INSERT statement.
TOP: Specify this keyword if you want to insert a specific number or percent of rows from the top of the query result.
table_name | view_name | rowset function | common_table_expression: This specifies the name of the table, view, rowset function, or CTE in which you want to insert data.
WITH table_hints: You use this keyword to specify one or more table hints for the target table.
output_clause: You use this keyword to identify the rows affected by the INSERT statement.
VALUES: This keyword specifies the data values to be inserted. You must provide a value for each column specified in the column_list parameter. Otherwise, you must provide a value for each column in the table.
value_list: Values can be constants, variables, or expressions. If an expression is used, it cannot contain SELECT or EXECUTE. You can specify DEFAULT to have a column use its default value, or use NULL to set a column value to NULL.
select_statement: If a SELECT statement is used, each result set must be compatible with the table columns or the column list.
DEFAULT: When you use DEFAULT, the SQL Server Database Engine inserts the default value, or (if the column allows NULL) a NULL value is inserted to the column.
execute_statement: This can be any valid EXECUTE statement that returns data with a SELECT statement. You can also use EXECUTE to execute a stored procedure that returns data with a SELECT statement. If an EXECUTE statement is used, each result set must be compatible with the columns of the table or the column list.
dml_table_source: This can be any valid DML statement that returns the affected rows in an OUTPUT clause.
DEFAULT_VALUE: When you use DEFAULT_VALUE, SQL Server inserts a new row with the default values defined for each column.

The INSERT examples

This section describes the various forms of INSERT statements used to insert data into a SQL Server database table. To illustrate the INSERT statement, create the dbo.CustomProducts table within the AdventureWorks2012 database. Here is the code to create this table:

USE [AdventureWorks2012];
GO
IF OBJECT_ID(N'dbo.CustomProducts', N'U') IS NOT NULL
    DROP TABLE [dbo].[CustomProducts];
GO

CREATE TABLE [dbo].[CustomProducts]
    (
      [ProductID] [int] IDENTITY(1, 1)
                        NOT NULL,
      [ProductName] [varchar](50) NULL
                 DEFAULT ('Anonymous'),
      [ProductCategory] [varchar](50) NULL
                 DEFAULT ('Anonymous'),
      [ListPrice] [money] NOT NULL
                 DEFAULT (1.0),
      [ListPriceCurrency] VARCHAR(30)
                 DEFAULT ('US Dollar'),
      [SellStartDate] [datetime] NOT NULL
                 DEFAULT CURRENT_TIMESTAMP,
      [SellEndDate] [datetime] NULL
    )
ON [PRIMARY];
GO

Example 1 – insert a single row into a SQL Server database table

In Object Explorer, enter and execute the following INSERT statement to add a single row to the dbo.CustomProducts table:

INSERT  INTO [dbo].[CustomProducts]
         ([ProductName],
          [ProductCategory],
          [ListPrice],
          [ListPriceCurrency],
          [SellStartDate],
          [SellEndDate]
         )
VALUES   (N'iPhone 5s',
          N'Gadget',
          25,
          N'GB',
          CURRENT_TIMESTAMP,
          DEFAULT
         );
GO

Execute the following code to show the new row:

USE [AdventureWorks2012];
GO

SELECT * FROM [dbo].[CustomProducts];
GO

The following screenshot shows the new row:

You can also use the INSERT statement to insert multiple rows in a table. This is done by providing a comma-delimited list of values for each row in the VALUE clause. For example, enter and execute the following T-SQL code to add three rows to the dbo.CustomProducts table:

USE [AdventureWorks2012];
GO

INSERT  INTO [dbo].[CustomProducts]
        ( [ProductName], [ProductCategory], [ListPrice], [ListPriceCurrency],
          [SellStartDate], [SellEndDate] )
VALUES  (N'Samsung Galaxy S5', N'Gadget', 426, N'GB', CURRENT_TIMESTAMP, DEFAULT )
       ,(N'HTC One (M8)', N'Gadget', 609, N'USD', CURRENT_TIMESTAMP, DEFAULT ),
        (N'Nokia Lumia 1520', N'Gadget', 529, N'USD', CURRENT_TIMESTAMP,DEFAULT );
GO

You can query the dbo.CustomProducts table again to verify the new rows inserted with the multirow INSERT statement. The result will be similar to the one shown in the following screenshot:

Example 1 – insert a single row into a SQL Server database table

Example 2 – INSERT with the SELECT statement

You can also use the SELECT statement within an INSERT statement to insert data rows that already exist in the same table, other tables, or even the tables of a different database. For example, the following INSERT statement uses the SELECT statement to insert rows in the dbo.CustomProducts table:

USE [AdventureWorks2012];
GO

INSERT  INTO [dbo].[CustomProducts]
        ( [ProductName], [ProductCategory], [ListPrice], [ListPriceCurrency],
          [SellStartDate], [SellEndDate])
SELECT  p.[Name],
        pc.[Name],
        [ListPrice],
        N'USD',
        [SellStartDate],
        [SellEndDate]
FROM    [Production].[Product] p
        INNER JOIN [Production].[ProductCategory] pc
        ON p.[ProductSubcategoryID] = PC.ProductCategoryID;
GO

Example 3 – INSERT with the EXEC statement

As mentioned in the preceding section, you can insert data into a table from the results of the EXECUTE statement. For example, enter and execute the following T-SQL code to insert the xp_msver stored procedure output into a temporary table:

USE [AdventureWorks2012];
GO

IF OBJECT_ID(N'Tempdb..#xp_msver') IS NOT NULL
DROP TABLE #xp_msver
GO

CREATE TABLE #xp_msver
    (
      [idx] [int] NULL,
      [c_name] [varchar](100) NULL,
      [int_val] [float] NULL,
      [c_val] [varchar](128) NULL
    )

INSERT  INTO #xp_msver
EXEC ('[master]..[xp_msver]'),
GO

Example 4 – explicitly inserting data into the IDENTITY column

By default, the SQL Server Database Engine generates the values for the IDENTITY columns. You cannot explicitly insert values into the IDENTITY property columns, unless you specify the IDENTITY_INSERT option. For example, SQL Server generates the following error when you attempt to insert a value into the IDENTITY property column:

Msg 544, Level 16, State 1, Line 27
Cannot insert explicit value for identity column in table 'CustomProducts' when IDENTITY_INSERT is set to OFF.

To explicitly insert values into the IDENTITY property column, use the IDENTITY_INSERT option. The basic syntax for this command is as follows:

SET IDENTITY_INSERT [database.[owner.]]table ON|OFF

For example, the following T-SQL code uses the IDENTITY_INSERT option and inserts an explicit value into the ProductID column:

USE [AdventureWorks2012];
GO

SET IDENTITY_INSERT [dbo].[CustomProducts] ON;

INSERT  INTO [dbo].[CustomProducts]
        ( [ProductID] ,
          [ProductName] ,
          [ProductCategory] ,
          [ListPrice] ,
          [ListPriceCurrency] ,
          [SellStartDate] ,
          [SellEndDate]
        )
VALUES  ( 110 ,
          N'Samsung Galaxy S4',
          N'Gadget',
          200,
          N'GB',
          CURRENT_TIMESTAMP,
          DEFAULT
        );

SET IDENTITY_INSERT [dbo].[CustomProducts] OFF;
GO

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for 4. Data Modification with SQL Server Transact-SQL Statements

Create new playlist

Sign In

Sign Up