INSERT and SELECT INTO

Question 1

Which SQL statements can you use to insert data?

Answer 1

1. INSERT VALUES
2. INSERT SELECT
3. INSERT EXEC
4. SELECT INTO

SQL Server 70-461 10-01

Question 2

Which of the four INSERT statements require that you specify the target column names?

Answer 2

None, but it is considered best practice to do so otherwise the EXEC, VALUES or SELECT portion of an INSERT statement has to specify the columns in column definition order and if the underlying table definition changes, errors could be encountered or values could be put in the wrong columns if the column order in the EXEC, VALUES or SELECT portion of the INSERT statement isn’t changed to match.

SQL Server 70-461 10-01

Question 3

Do you need to specify a value for a column with an IDENTITY property in an INSERT statement?

Answer 3

No, the value will generate automatically.

SQL Server 70-461 10-01

Question 4

How can you set the value for a column that has an IDENTITY property using an INSERT statement?

Answer 4

Turn on a session option called IDENTITY_INSERT with the following syntax:

SET IDENTITY_INSERT ON;

Remember to turn this option off when you’re done. Also, note that you have to have VERY strong permissions to turn this option off and on. You need to own the table or have ALTER
permissions on the table.

SQL Server 70-461 10-01

Question 5

What is another way, besides the IDENTITY property, that data can be inserted into a column automatically using an INSERT statement?

Answer 5

A value can be assigned automatically due to the column having a DEFAULT constraint.

SQL Server 70-461 10-01

Question 6

What is the syntax in an INSERT VALUES statement for using the DEFAULT constraint value?

Answer 6

Option 1
- You can omit the column in the INSERT VALUES statement entirely and the DEFAULT value will automatically be assigned.
- Example: INSERT INTO schema.TableName (column 2, column 3) VALUES (value 2, value 3);

Option 2
- You can explicitly add the column name in the columns list and the word DEFAULT in the values list and the DEFAULT value will automatically be assigned.
- Example: INSERT INTO schema.TableName (column 1, column 2, column 3) VALUES (DEFAULT, value 2, value 3); “

SQL Server 70-461 10-01

Question 7

What is the syntax in an INSERT SELECT statement for using the DEFAULT constraint value?

Answer 7

Just omit the column name in the target column list and of course it would not be included in the SELECT portion of the statement.

SQL Server 70-461 10-01

Question 8

What happens if you don’t include a column in the target column list of an INSERT statement?

Answer 8

• First, SQL Server checks to see if the column gets its value automatically from an IDENTITY property or DEFAULT constraint.
• Second, if there is no IDENTITY property or DEFAULT constraint SQL Server will check to see if the column allows NULLS. If it does, SQL Server assumes a NULL.
• Third, if there is no IDENTITY property, DEFAULT constraint and NULL’s aren’t allowed, SQL Server will generate an error.”

SQL Server 70-461 10-01

Question 9

What is the proper syntax for an INSERT VALUES statement?

Answer 9

INSERT INTO schema.TableName (column 1, column 2, column 3) VALUES (value 1, value 2, value 3);

SQL Server 70-461 10-01

Question 10

How many rows can be inserted with the INSERT VALUES statement?

Answer 10

1 or more

SQL Server 70-461 10-01

Question 11

What is the syntax for inserting multiple rows using an INSERT VALUES statement?

Answer 11

Separate each row with a comma.

Ex.
INSERT INTO schema.TableName (Column 1, Column 2, Column 3) VALUES
(8, ‘USA’, ‘Minnesota’),
(2, ‘USA’, ‘California’),
(3, ‘USA’, ‘Colorado’);

SQL Server 70-461 10-01

Question 12

What happens if one row fails to insert in an INSERT VALUES statement with multiple rows?

Answer 12

If even one row fails to insert to the target table, then the entire statement will fail and no rows will be entered into the target table since it is considered one transaction .

SQL Server 70-461 10-01

Question 13

How is the INSERT VALUES statement different from the INSERT SELECT statement?

Answer 13

With the INSERT VALUES statement you explicitly spell out the values. With the INSERT SELECT statement the values are obtained via a SELECT statement.

SQL Server 70-461 10-01

Question 14

What is the proper syntax for an INSERT SELECT statement?

Answer 14

INSERT INTO schema.TableName (column 1, column 2, column 3)
SELECT column a, column b, column c FROM schema.TableName;

SQL Server 70-461 10-01

Question 15

What does the INSERT EXEC statement do?

Answer 15

It inserts the result set (or sets) returned by a dynamicbatch or a stored procedure into the specified target table.

SQL Server 70-461 10-01

Question 16

Example of an INSERT EXEC statement.

Answer 16

INSERT INTO Sales.MyOrders (orderid, custid, empid, orderdate, shipcountry, freight)
EXEC Sales.OrdersForCountry@country = N’Portugal’;

Procedure that is referred to in the statement, just to understand the context:
CREATE PROC Sales.OrdersForCountry
@country AS NVARCHAR(15)
AS

SELECT orderid, custid, empid, orderdate, shipcountry, freightFROM Sales.OrdersWHERE shipcountry = @country;GO

SQL Server 70-461 10-01

Question 17

Does INSERT EXEC works if the source dynamic batch or stored procedure has morethan one query?

Answer 17

Yes, but only if all queries return result sets that are compatible withthe target table definition.

SQL Server 70-461 10-01