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 <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()
)

The easiest INSERT statement goes something like this:

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')

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%';

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%';

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.

More Posts - Website - Twitter