Using the INSERT Statement | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

The basis of adding information is the INSERT clause. This clause uses the following syntax:

 INSERT INTO [<ServerName>.][<DataBaseName>.]<SchemaName>.<TableName>            (<FieldName1>[,<FieldName2>])      VALUES            (<Value1>[,<Value2>..])

If you execute the INSERT sentence with a connection to the current database, you can simply use the <SchemaName>.<TableName> form without including the server and database names .

When you want to insert a record, you must carefully consider some column limitations.

A column defined using the identity attribute cannot be inserted. You do not enlist the column in the field list and therefore do not assign a value for it.
Timestamp is not an assignable datatype.
Remember the constraints defined for each column.
Use CONVERT or CAST when necessary.

Creating an INSERT Sentence with SQL Server Management Studio

You can use Microsoft SQL Server Management Studio to help you create an INSERT sentence.

Creating an INSERT Sentence

From Object Explorer in SQL Server Management Studio, right-click the desired table.
Choose Script Table As, and then choose Insert To from the context menu.
Select New Query Editor Window. SQL Server Management Studio will create a template for you.
From the Query menu, select Specify Values For Template Parameters.

You can now define your own values. The wizard takes care of such things as identity or timestamp fields, but allows you to define the rest of the values.

Look at the INSERT sentence generated for the Sales.SalesOrderHeader data table in the AdventureWorks database.

 INSERT INTO [AdventureWorks].[Sales].[SalesOrderHeader]            ([RevisionNumber]            ,[OrderDate]            ,[DueDate]            ,[ShipDate]            ,[Status]            ,[OnlineOrderFlag]            ,[PurchaseOrderNumber]            ,[AccountNumber]            ,[CustomerID]            ,[ContactID]            ,[SalesPersonID]            ,[TerritoryID]            ,[BillToAddressID]            ,[ShipToAddressID]            ,[ShipMethodID]            ,[CreditCardID]            ,[CreditCardApprovalCode]            ,[CurrencyRateID]            ,[SubTotal]            ,[TaxAmt]            ,[Freight]            ,[Comment]            ,[rowguid]            ,[ModifiedDate])      VALUES            (<RevisionNumber, tinyint,>            ,<OrderDate, datetime,>            ,<DueDate, datetime,>            ,<ShipDate, datetime,>            ,<Status, tinyint,>            ,<OnlineOrderFlag, Flag,>            ,<PurchaseOrderNumber, OrderNumber,>            ,<AccountNumber, AccountNumber,>            ,<CustomerID, int,>            ,<ContactID, int,>            ,<SalesPersonID, int,>            ,<TerritoryID, int,>

 ,<BillToAddressID, int,>            ,<ShipToAddressID, int,>            ,<ShipMethodID, int,>            ,<CreditCardID, int,>            ,<CreditCardApprovalCode, varchar(15),>            ,<CurrencyRateID, int,>            ,<SubTotal, money,>            ,<TaxAmt, money,>            ,<Freight, money,>            ,<Comment, nvarchar(128),>            ,<rowguid, uniqueidentifier,>            ,<ModifiedDate, datetime,>)

Using Special Values for Row Insertion

You may want to use special values when inserting a row. In the SalesOrderHeader example, the rowguid column requires a value of uniqueidentifier as the datatype. In that case, you can use the NEWID() function. DueDate is another good example. If you want to assign the actual date and time to this column, you can use the GETDATE() function. If a column allows NULL and you do not want to specify a value, you can use the NULL keyword. Finally, if you look at the data definition language for this table, the ModifiedDate column has an assigned default value that refers to the GETDATE() function. You can simply use the DEFAULT keyword to assign the default value.

Note	You can find all of the script samples for this chapter in \Ch10\Sample Codes\Chapter 10 Scripts in the sample files.

The following code sample (Insert Special Values.sql) demonstrates how to use these special values.

 INSERT INTO [AdventureWorks].[Sales].[SalesOrderHeader]            ([RevisionNumber]            ,[OrderDate]            ,[DueDate]            ,[ShipDate]            ,[Status]            ,[OnlineOrderFlag]            ,[PurchaseOrderNumber]            ,[AccountNumber]            ,[CustomerID]            ,[ContactID]            ,[SalesPersonID]            ,[TerritoryID]            ,[BillToAddressID]            ,[ShipToAddressID]            ,[ShipMethodID]            ,[CreditCardID]            ,[CreditCardApprovalCode]            ,[CurrencyRateID]

 ,[SubTotal]            ,[TaxAmt]            ,[Freight]            ,[Comment]            ,[rowguid]            ,[ModifiedDate])      VALUES            (0 --RevisionNumber            ,GETDATE() -- Actual date            ,DATEADD(day,5,GETDATE()) -- five days from now            ,null -- ShipDate is not yet known            ,1            ,1            ,null -- order number            ,null -- AccountNumber            ,1 -- CustomerID            ,1 -- ContactID            ,1 -- SalesPersonID            ,1 -- TerritoryID            ,1 -- BillToAddressID            ,1 -- ShipToAddressID            ,1 -- ShipMethodID            ,1 -- CreditCardID            ,'ok' -- CreditCardApprovalCode            ,1 -- CurrencyRateID            ,100 -- SubTotal            ,5 -- TaxAmt            ,0 -- Freight            ,null -- Comment            ,NEWID() -- rowguid            ,DEFAULT -- ModifiedDate)

Using Other Forms of the INSERT Statement

There may be times when you merely want to insert a row into a table. If all of the columns have defined default values or allow NULLs, you can insert a row using the following syntax:

 INSERT INTO <TableName> DEFAULT VALUES

If you want to keep summarized information in a separate table for the purpose of results analysis, you can use code to derive that data from the detailed table. You may instead want to increase the performance of your queries by executing a process to accumulate the information in another database.

The following code sample (Create TestTable.sql) adds a data table to the AdventureWorks database.

 USE [AdventureWorks] GO CREATE TABLE [Sales].[TotalSales]([TotalSalesId] [int] IDENTITY(1,1) NOT NULL,    [Period] [char](7)  NOT NULL,    [CustomerID] int  NOT NULL,    [Total] [money] NOT NULL,  CONSTRAINT [PK_TotalSales] PRIMARY KEY CLUSTERED ([TotalSalesId] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] GO

The data needed to fill this table can be obtained from the following sentence.

 SELECT   CONVERT(varchar(7), OrderDate, 120) AS OrderMonth,       CustomerID,       SUM(TotalDue) AS TotalDue FROM   Sales.SalesOrderHeader GROUP BY    CustomerID,    CONVERT(varchar(7), OrderDate, 120)

In addition, you can insert the results into the new table with a simple Transact-SQL (T-SQL) statement, such as the following:

 INSERT INTO Sales.TotalSales (Period,CustomerID,Total) SELECT   CONVERT(varchar(7), OrderDate, 120) AS OrderMonth,       CustomerID,       SUM(TotalDue) AS TotalDue FROM   Sales.SalesOrderHeader GROUP BY    CustomerID,    CONVERT(varchar(7), OrderDate, 120)

Notice that the values to be inserted must match the datatypes of the destination columns. Also, because the TotalSalesId column contains the identity attribute, it is not managed by the script and will be updated automatically.