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
Server’s 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-SQL’s 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:
Msg 110, Level 15, State 1, Line 1
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.
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.
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’);
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.
Now let’s 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 Designer’s 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.
»
See All Articles by Columnist Don Schlichting
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply