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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
[ WITH <common_table_expression> [ <strong>,</strong>...<em>n</em> ] ] INSERT [ TOP <strong>(</strong> <em>expression</em> <strong>)</strong> [ PERCENT ] ] [ INTO ] { <object> | <em>rowset_function_limited</em> [ WITH <strong>(</strong> <Table_Hint_Limited> [ ...n ] <strong>)</strong> ] } { [ <strong>(</strong> <em>column_list</em> <strong>)</strong> ] [ <OUTPUT Clause> ] { 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> | <dml_table_source> | DEFAULT VALUES } } [; ] <strong><object> ::=</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> } <dml_table_source> ::= SELECT <select_list> FROM <strong>(</strong> <dml_statement_with_output_clause> <strong>)</strong> [AS] <em>table_alias</em> [ ( <em>column_alias</em> [ ,...<em>n</em> ] ) ] [ 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:
12345678 <span style="color: #0000ff;">CREATE TABLE</span> dbo.CountryList(CountryID <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">IDENTITY</span>(1,1),CountryName <span style="color: #0000ff;">varchar</span>(50) NOT NULL,CountryCode2 <span style="color: #0000ff;">char</span>(2) NOT NULL,CountryCode3 <span style="color: #0000ff;">char</span>(3) NOT NULL,lastUpdated <span style="color: #0000ff;">smalldatetime</span> NOT NULL <span style="color: #0000ff;">default</span> <span style="color: #ff00ff;">getdate</span>())
The easiest INSERT statement goes something like this:
1 <span style="color: #0000ff;">INSERT INTO</span> TABLENAME(Col1,Col2,...,Colx) <span style="color: #0000ff;">VALUES</span> (<span style="color: #ff0000;">'Val1'</span>,<span style="color: #ff0000;">'Val2'</span>,...,<span style="color: #ff0000;">'Valx'</span>)
Here is a sample using the CountryList table:
123 <span style="color: #0000ff;">INSERT INTO</span> CountryList(CountryName,CountryCode2,CountryCode3)<span style="color: #0000ff;">VALUES</span>(<span style="color: #ff0000;">'Atlantis'</span>,<span style="color: #ff0000;">'AT'</span>,<span style="color: #ff0000;">'ATL'</span>)
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.
1234 <span style="color: #0000ff;">SELECT</span> *<span style="color: #0000ff;">INTO</span> CountryList2<span style="color: #0000ff;">FROM</span> countrylist<span style="color: #0000ff;">WHERE</span> countryName like <span style="color: #ff0000;">'M%'</span>;
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.
1234 <span style="color: #0000ff;">INSERT INTO</span> CountryList2(CountryName,CountryCode2,CountryCode3,lastUpdated)<span style="color: #0000ff;">SELECT</span> CountryName,CountryCode2,CountryCode3,<span style="color: #ff00ff;">GETDATE</span>()<span style="color: #0000ff;">FROM</span> countrylist<span style="color: #0000ff;">WHERE</span> countryName like <span style="color: #ff0000;">'A%'</span>;
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.