Importing Data into SQL Server – Using the INSERT statement

Sometimes, the easiest and quickest way to get data into SQL Server is to simply use an INSERT statement. Here is what the INSERT statement looks like in Books Online(BOL)

[ WITH &lt;common_table_expression&gt; [ <strong>,</strong>...<em>n</em> ] ]
INSERT
    [ TOP <strong>(</strong> <em>expression</em> <strong>)</strong> [ PERCENT ] ]
    [ INTO ]
    { &lt;object&gt; | <em>rowset_function_limited</em>
      [ WITH <strong>(</strong> &lt;Table_Hint_Limited&gt; [ ...n ] <strong>)</strong> ]
    }
{
    [ <strong>(</strong> <em>column_list</em> <strong>)</strong> ]
    [ &lt;OUTPUT Clause&gt; ]
    { VALUES <strong>(</strong> ( { DEFAULT | NULL | <em>expression</em> } [ <strong>,</strong>...n ] <strong>)</strong> [ <strong>,</strong>...<em>n</em> ] )
    | <em>derived_table</em>
    | <em>execute_statement</em>
    | &lt;dml_table_source&gt;
    | DEFAULT VALUES
    }
}
[; ]

<strong>&lt;object&gt; ::=</strong>
{
    [ <em>server_name </em>. <em>database_name</em> . <em>schema_name </em>.
      | <em>database_name </em>.[ <em>schema_name</em> ] .
      | <em>schema_name</em> .
    ]
        <em>table_or_view_name</em>
}

&lt;dml_table_source&gt; ::=
    SELECT &lt;select_list&gt;
    FROM <strong>(</strong> &lt;dml_statement_with_output_clause&gt; <strong>)</strong>
                      [AS] <em>table_alias</em> [ ( <em>column_alias</em> [ ,...<em>n</em> ] ) ]
        [ WHERE &lt;search_condition&gt; ]
    [ OPTION ( &lt;query_hint&gt; [ ,...n ] ) ]

[ WITH <common_table_expression> [ ,...n ] ]

INSERT

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ]

{ <object> | rowset_function_limited

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

}

{

[ ( column_list ) ]

[ <OUTPUT Clause> ]

{ VALUES ( ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] )

| derived_table

| execute_statement

| <dml_table_source>

| DEFAULT VALUES

}

[; ]

<object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name

}

<dml_table_source> ::=

SELECT <select_list>

FROM ( <dml_statement_with_output_clause> )

[AS] table_alias [ ( column_alias [ ,...n ] ) ]

[ WHERE <search_condition> ]

[ OPTION ( <query_hint> [ ,...n ] ) ]

Ok, let stick to the basic and learn some quick insert techniques that you can use today. Recall that we are using the CountryList table, which we created in earlier examples. Here is the script to create the table:

CREATE TABLE dbo.CountryList ( CountryID int IDENTITY(1,1) ,CountryName varchar(50) NOT NULL ,CountryCode2 char(2) NOT NULL ,CountryCode3 char(3) NOT NULL ,lastUpdated smalldatetime NOT NULL default getdate() )

1
2
3
4
5
6
7
8

CREATE TABLE dbo.CountryList
(
CountryID int IDENTITY(1,1)
,CountryName varchar(50) NOT NULL
,CountryCode2 char(2) NOT NULL
,CountryCode3 char(3) NOT NULL
,lastUpdated smalldatetime NOT NULL default getdate()
)

The easiest INSERT statement goes something like this:

INSERT INTO TABLENAME(Col1,Col2,...,Colx) VALUES ('Val1','Val2',...,'Valx')

1

INSERT INTO TABLENAME(Col1,Col2,...,Colx) VALUES ('Val1','Val2',...,'Valx')

Here is a sample using the CountryList table:

INSERT INTO CountryList(CountryName,CountryCode2,CountryCode3) VALUES ('Atlantis','AT','ATL')

1
2
3

INSERT INTO CountryList(CountryName,CountryCode2,CountryCode3)
VALUES
('Atlantis','AT','ATL')

Recall that the country table has an IDENTITY Column, so we cannot specifiy a value for the CountryID*. Also, the lastUpdate column has a default value, so we do not have to specify a value for that column, either. Lastly, we are enumerating the columns, so we will use the VALUES keyword.

Another technique is inserting data from one table into a new table by using the INTO keyword. INTO will take the results from a query, including column names and column types, and insert the results into a new table.

SELECT * INTO CountryList2 FROM countrylist WHERE countryName like 'M%';

1
2
3
4

SELECT *
INTO CountryList2
FROM countrylist
WHERE countryName like 'M%';

In this sample, we are selecting every row where the country name starts with an M and inserting those rows into the table CountryList2, which does not exist at runtime. Notice, that we do not have to use the VALUES keyword. What we end up with is a table with the same column names, column types and the IDENTITY column on the CountryID. However, the lastUpdated column will not have the default value specified in the CREATE statement of CountryList table. Also, note that you cannot use the INTO keyword if the table already exists.

Next, we can use the SELECT and INSERT statement to move data from one table to another. We will use the CountryList table as a source and the CountryList2 table that we just created, as a destination.

INSERT INTO CountryList2(CountryName,CountryCode2,CountryCode3,lastUpdated) SELECT CountryName,CountryCode2,CountryCode3,GETDATE() FROM countrylist WHERE countryName like 'A%';

1
2
3
4

INSERT INTO CountryList2(CountryName,CountryCode2,CountryCode3,lastUpdated)
SELECT CountryName,CountryCode2,CountryCode3,GETDATE()
FROM countrylist
WHERE countryName like 'A%';

Notice here, that we had to specify a value for the lastUpdated column. This is because the lastUpdated column in the CountryList2 does not have a default value specified. However, the column retains the NOT NULL from the original CountryList table.

* Actually, you can specify a value for IDENTITY Columns. You need to enable IDENTITY_INSERT.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

Rudy Rodarte . com

Follow Rudy as he programs, travels and designs.

Importing Data into SQL Server – Using the INSERT statement

Rudy