The INSERT statement lets you add one or more rows to a table or view in a SQL Server database. The statement is one of the primary data modification language (DML) statements available in Transact-SQL, along with UPDATE, MERGE, and DELETE. You can use the INSERT statement to add data that you specifically define, or you can add data that you retrieve from other tables or views. You can also include an OUTPUT clause in your INSERT statement to capture the statement’s results for auditing or verification purposes. In this article, I explain how to use the INSERT statement to add data to SQL Server tables.

The examples I show are based on the AdventureWorks2008 sample database, installed on a local instance of SQL Server 2008. However, you can run most of these examples against the AdventureWorks database in SQL Server 2005, with minor modifications, such as changing the database name. Where appropriate, I note which features are not supported in 2005. NOTE: One of the views I reference in the AdventureWorks2008 database (the SalesPerson view) includes the BusinessEntityID column.

This column is referred to as the SalesPersonID column in the AdventureWorks database. Performing a Basic Insert In a basic INSERT statement you must specify the name of the target table and the data values you want to insert into that table.

When necessary, you should qualify that table name with the name of the server, database, or schema. To demonstrate how to create a basic INSERT statement, I first used the following code to create the SalesStaff1 table in the AdventureWorks2008 database. INSERT INTO SalesStaff1 VALUES ( 1, 'Stephen', 'Jiang' ); Notice that the statement begins with the INSERT keyword, followed by the INTO keyword. The INTO keyword is optional. Often you’ll see it used. Often you will not. After the INSERT keyword and optional INTO keyword, you specify the name of the target table, which in this case is SalesStaff1.

I decided to use BULK INSERT to implement the solution. The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script. In pre-7 versions the only way you could access bcp functionality was from a command prompt.

Next you specify the VALUES keyword and then the values themselves. 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. In this case, 1 is inserted into the table’s first column (StaffID), Stephen is inserted into the second column (FirstName), and Jiang is inserted into the third column (LastName). That’s all there is to inserting a row into a table. However, as is often the case, 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. ( 5, 'Garrett', 'Vargas' ); As you can see, you must still specify the INSERT keyword, the name of the target table, and the VALUES keyword. However, instead of a single set of parentheses, you now have a set for each row to be inserted into the table.

At indiatyping.com we provide all type of Hindi font to download at free. 4clipika hindi fonts free.

The value sets are then separated with commas. Now when you run the statement, all four rows will be added to the SalesStaff1 table.

Inserting Specific Column Values The examples in the previous section are INSERT statements at their simplest. They assume that you will add one value to each column in the table for each row you plan to insert.

However, it’s not always that easy. In many cases, you’ll want to insert values into specific columns, but not all columns. For example, if you have an IDENTITY column, you might not want to insert a value into that, but instead allow the database engine to generate the next value automatically. However, the INSERT statement, as it is used in the previous examples, requires one value for each column.

If you do not specify an appropriate value for each column, you will generate an error. To address this issue, the INSERT statement supports an additional component that lets you specify which columns should receive values. The values you specify are then inserted into those columns. Columns that are not specified are assumed to generate their own values, as is the case with IDENTITY columns, calculated columns, or columns for which a default value has been defined. For example, the following code creates the SalesStaff2 table, which includes columns that do not require values to be explicitly added. FROM SalesStaff2; Notice that I add the list of columns after I specify the INSERT INTO keywords and the name of the target table. The columns are enclosed in parentheses and separated with commas.