INSERT with SQL 2008
April 3, 2009
This article will explore the various methods of using an INSERT statement with SQL Server 2008. After database Tables are created, a method to perform basic data manipulation tasks like inserting, modifying and deleting data is needed. Such data manipulation is accomplished through SQL Servers own dialect of Structured Query Language (SQL) called Transact SQL or (T-SQL). TSQL Commands can be executed by typing them in directly to SQL Server, or graphically though the SQL Server Management Studio. The SQL Server Management Studio (SSMS) is an easy to use and intuitive graphical tool that lets you build and manage your database graphically. This article will explore the INSERT command of T-SQLs Data Manipulation Language (DML).
T-SQL Statements can be typed directly into SQL Server by opening a new Query Window. To launch a Query Window, open the SQL Server Management Studio, highlight the correct Database, and then click the New Query button on the top menu. A blank Query Window will open.
Let's start with the most basic syntax of INSERT command.
INSERT [INTO] table_name [column_list] VALUES default values|values_list INSERT INTO Customers (CustID, CustName) VALUES ('Cust1', 'Smith Company');
The INSERT command must be followed by the table name of where you want to add data. The INTO keyword is optional. However, INTO makes the statement more readable. In this example, two columns of information are entered. The order the columns appear in the VALUES line must match the order of the INTO statement. So both the INTO and VALUES lines must match in the number of items and order listed. If the two lines do not match, an error like the following will be thrown:
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
All Values Entered
If a value for every column of information is going to be inserted, then it is not necessary to specify the name of each column. But, to use this syntax, every Table column of information must be entered in the same order they are listed in the Table definition.
INSERT INTO Customers VALUES ('Cust1', 'Smith Company', '123 West 51 st', 'Tampa', 'FL');
Multiple Insert Rows
SQL Server 2008 supports the option of inserting multiple records in one statement. Each row of data is followed by a comma until reaching the last row where the INSERT command is completed like normal.
INSERT INTO Customers (CustID, CustName) VALUES ('Cust1', 'Smith Company'), ('Cust2', 'Perform Company'), ('Cust3', 'Test Inc');
Other version of SQL Server require a separate statement to be executed for each record insert.
If a Column has been defined with a Default value, that value will be inserted into each new row if nothing else is explicitly passed in with the INSERT statement. For example, this table has a default value of WI for the State column.
Executing an INSERT statement like the one shown below, where no value for State is explicitly entered, will default to WI.
INSERT INTO Customers (CustID, CustName) VALUES ('Cust1', 'Smith Company');
INSERT INTO Customers (CustID, CustName) SELECT CustID, CustName FROM LastYearSales
Insert values can be derived from a select statement. In this example, the CustID and CustName were obtained from existing data rather than explicitly typed in. The usual INSERT rules apply such as each column specified in the INTO part of the statement must have a matching Select return.
You cannot insert NULL value or duplicate value to any primary key column.
Foreign key values identify athe relationship between two tables. While inserting or modifying values in the column that holds foreign key constraint, the corresponding values must be present in the related table.
IDENTITY Columns that are auto-filled cannot be inserted with a specific value without first executing a special Insert Identity command; SET IDENTITY_INSERT ON. For example, the following statement demonstrates inserting into an IDENTITY Field. Test is the Table Name.
SET IDENTITY_INSERT test ON INSERT INTO test (myID) VALUES (4) SET IDENTITY_INSERT test OFF
The value inserted does not need to be the next number the Identity column normally would have used. Meaning if there were three rows previously entered, with IDs of 1, 2, and 3, any number 4 or greater could be used. We are not forced to use 4. We could use 5, 6 or any greater number we wish. Doing so will not cause a problem. The next time auto identity is invoked; it will look for the greatest number then add one.
Management Studio Insert
Now lets explore how to insert data using SQL Server Management Studio's graphical interface. Right click against the Table in the Object Explorer. Select Edit Top 200 Rows from the context menu.
A data grid with populated data will be displayed as follows. This data grid is very similar to MS Excel and you can enter data in a similar way. The starred row on the left-hand side shows the insertion point to the table. While entering data you must specify acceptable values in accordance with constraints that are set for each column.
Another graphical way to insert data into a table is using Design View of Query Editor. Right click in a black Query Window and then select Query, Design Query in Editor as shown below.
From the Add Tables dialog box, select the tables you want to insert or update data. Right click any where in the Query Designer and Select Change Type, Insert Values from the context menu.
As you enter data in the Column and New Value in the Query Designers middle pane, the INSERT INTO script is built. Once you enter the values click OK to exit Design view. Press F5 or Execute button to insert data.
To edit your statement after it has been run once, highlight the entire statement, right click anywhere in the highlighted area, and then select Design in Query Editor. If the entire statement is not highlighted, then a new design window will open rather than editing the existing code.
The INSERT Statement enables values to be entered into a SQL Server Table. Statements can be typed in or created graphically with Query Designer. Values can be explicitly type or derived from some other SELECT statement. New in SQL Server 2008 is the ability to enter multiple rows in a single statement.