1. How do I insert literal values or constants to a table or view?
Answer:
To insert just literal values or constants into a table or view, you will use
the simplest form of the INSERT statement, as follows:
INSERT INTO <table_name> | <view_name> [( <column_list> )]
VALUES ( <values_list> )
The VALUES keyword specified the values of one row of a table. The values
are specified as a comma-separated list of scalar expressions whose data type,
precision, and scale must be the same as or implicitly convertible to the
corresponding column in the column list. If a column list is not
specified, the values must be specified in the same sequence as the columns in
the table or view.
To illustrate on how to use the INSERT INTO ... VALUES, given a Customers table:
CREATE TABLE [dbo].[Customers] (
[ID] INT NOT NULL,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL)
To insert a row in this table using literal values, you issue the following:
INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName] )
VALUES (1, 'Bill', 'Gates')
Since the column list is optional, you can also do the following. Just
make sure that the sequence of the values being inserted matches the sequence
of the columns in the table.
INSERT INTO [dbo].[Customers] VALUES (2, 'Larry', 'Ellison')
Although the column list is optional, it is recommended that it is always
provided when doing an INSERT. This is to make sure that your INSERT
statement will still work and each column will receive the correct value even
if new columns are added or inserted between other columns in the table.
If another column is added in the table and the same INSERT command is executed
without the column list, the following error will be encountered:
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
2. How do I insert the rows of a table into another table?
Answer:
To insert values into a table from one or more tables or views, you will use the
SELECT subquery in the INSERT statement as follows:
INSERT INTO <table_name> | <view_name> [ ( <column_list> ) ]
SELECT <column_list> FROM <table_name> | <view_name>
Using a SELECT subquery lets more than one row be inserted at one time.
The column list of the SELECT subquery must match the column list of the
INSERT statement. If no column list is specified, the select list must
match the columns in the table or view being inserted into. The SELECT
subquery can reference one or more tables or views using joins.
To illustrate on how to use the INSERT statement with a SELECT subquery,
assuming that you have 2 tables called [dbo].[Customers] and
[dbo].[NewCustomers] and you want to insert into the [dbo].[Customers] table
all records from the [dbo].[NewCustomers] table:
INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName] )
SELECT [ID], [FirstName], [LastName]
FROM [dbo].[NewCustomers]
Since the column list is optional, you can also do the following. Just
make sure that the sequence of the values being inserted matches the sequence
of the columns in the table.
INSERT INTO [dbo].[Customers]
SELECT [ID], [FirstName], [LastName]
FROM [dbo].[NewCustomers]
Although the column list is optional, it is recommended that it is always
provided when doing an INSERT. This is to make sure that your INSERT
statement will still work and each column will receive the correct value even
if new columns are added or inserted between other columns in the table.
If another column is added in the table and the same INSERT command is executed
without the column list, the following error will be encountered:
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
3. How do I insert into a table rows from another table and some literal values?
Answer:
To insert values into a table from one or more tables or views and with some
literal values for some columns, you will use the SELECT subquery in the INSERT
statement as follows:
INSERT INTO <table_name> | <view_name> [ ( <column_list> ) ] SELECT <column_list>
| <literal_value> FROM <table_name> | <view_name>
Using a SELECT subquery lets more than one row be inserted at one time.
The column list and literal values of the SELECT subquery must match the
column list of the INSERT statement. The SELECT subquery can reference
one or more tables or views using joins.
To illustrate on how to use the INSERT statement with a SELECT subquery,
assuming that you have 2 tables called [dbo].[Customers] and
[dbo].[NewCustomers] and you want to insert into the [dbo].[Customers] table
all records from the [dbo].[NewCustomers] table and setting the [StatusFlag] to
a value of 'New', the INSERT statement will look like the following:
INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName], [StatusFlag] )
SELECT [ID], [FirstName], [LastName], 'New'
FROM [dbo].[NewCustomers]
|