Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Basic Query Design Patterns-Inserting one record

From Wiki

Jump to: navigation, search

One of the most basic things any SQL Server developer needs to be able to do is to insert one record into a database.

This is accomplished through the first form of the INSERT statement

  1. INSERT tablename(list of column names)
  2.     VALUES (List of values to be inserted)

A sample query would be:

  1. INSERT dbo.Orders (CustomerID, OrderDate)
  2.     VALUES (1234, '20100424')

That seems terribly simple, but in practice it may not be quite so simple.

The first consideration is to always list the columns names. Yes, if you are inserting into every column of the table (except autogenerated ones), you can get away with not listing them. But this is a poor practice. Your insert will break the first time someone adds a column. Even worse, people have been known (for reasons which generally mean they are database illiterates) to rearrange the columns in a table. If someone does that, now your insert will insert the wrong data into the wrong columns or fail (fail being by far the better option).

If you are not inserting to all the columns of the table (except autogenerated ones), you must list them out or the insert will fail.

Another form of the single record insert is when you get the values from a defined parameter. Often these are the input parameters in a stored procedure for instance. You can also use system functions to provide the values. For example:

  1. DECLARE @CustomerID int
  2.     SET  @CustomerID = 1234
  3.     INSERT dbo.Orders (CustomerID, OrderDate)
  4.     VALUES (@CustomerID, get_date())

This would insert the current datetime as well as the value for the parameter @CustomerID.

The next thing you have to worry about is mismatched data types or data too long to fit into the column size. Especially when you get values from a parameter, you should take care to ensure the datatype of the parameter exactly matches the data type of the table. Further when you define the paremeter data types don't forget to add the size if is is part of the data type. Otherwise you may get unexpected truncation of data.

Another issue that arises with the single record insert is that often you want to the value of the autogenerated identity column returned to you, so that you can insert into a child table. There are several ways to accomplish this, some better than others.

In older versions of the SQL Server, the best way is to immediately ask for the scope_identity() to be returned and usually set to a variable so you can use it later. Now our code becomes something like this:

  1. DECLARE @CustomerID int
  2.     DECLARE @OrderID int
  3.     SET  @CustomerID = 1234
  4.     INSERT dbo.Orders (CustomerID, OrderDate)
  5.     VALUES (@CustomerID, get_date())
  6.     SET @OrderID = scope_identity()

In SQL Server 2008, you have a new choice. You can use the OUTPUT clause in order to return the ID and incidentally other columns if you need them as well. This is alittle more complicated but worth it for many situations. The pattern is:

  1. DECLARE @OUTPUT table( column definitions)
  2.     INSERT tablename(list of column names)
  3.         OUTPUT values from inserted table
  4.         INTO @OUTPUT
  5.     VALUES (List of values to be inserted)

To uderstand how to set this up, you need to understand that when SQL server inserts a record, it puts the values into a pseudotable called inserted which is accessible in triggers and now with the OUTPUT keyword. This includes the autogenerated values as well as the ones you defined inteh actual insert. To be able to use these values later, you should put them into a table variable, so first you have to define that. An example query:

  1. DECLARE @CustomerID int
  2.     DECLARE @OUTPUT table(OrderID int)
  3.     SET  @CustomerID = 1234
  4.     INSERT dbo.Orders (CustomerID, OrderDate)
  5.         OUTPUT inserted.OrderID
  6.         INTO @OUTPUT
  7.     VALUES (@CustomerID, get_date())

A word of caution, you will see many places where people say to use @@IDENTITY to get the value of the identity column. This is a bad choice becasue it will return the wrong value if someone ever puts a trigger on the table which also does an insert into a table with an identity value. Worse, it may not cause an error and you may find out months later that your related records are related to the wrong parent record. Do not use @@IDENTITY, it is a threat to the integrity of your data.

735 Rating: 2.2/5 (49 votes cast)