INSERT Statement (version 6.5)

Populates a local table with results returned from a procedure and makes it easier to import data from remote systems to the local server.

For additional syntax information for the INSERT statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

INSERT [INTO]
{table_name | view_name} [(column_list)]
    EXECute { procedure_name | @procedure_name_var}
    [[@parameter_name=] {value | @variable [OUTPUT] | DEFAULT}
    [, [@parameter_name =] {value | @variable [OUTPUT] | DEFAULT}]...]

where

table_name

Is the existing table in the local database to populate.

column_list

Is a list of one or more columns in which to insert data. The columns can be listed in any order, but the incoming data must be in the same order as the listed columns. The column_list must be enclosed in parentheses and delimited by commas.

The column_list is necessary only when some, but not all, columns in the table are to receive data. You can leave out items in the column_list as long as the omitted columns permit null values or the columns are associated with a default.

If column_list is not specified, all columns in the receiving table (in CREATE TABLE order) are assumed to be included.

procedure_name = {[[[server.]database.]owner.]procedure_name[;number] }

Is a user-defined procedure name.

procedure_name

Is a stored procedure. You can execute a local procedure, or you can execute a procedure that has been created in another database if you are its owner or have permission to execute it in that database. You can execute a procedure on another SQL Server if you have permission to use that server (remote access) and to execute the procedure in that database. If you specify a server name but do not specify a database name, SQL Server looks for the procedure in your default database.

;number

Is an optional integer used to differentiate procedures created by using the same name.

@procedure_name_var

Is the name of a locally defined variable that represents a stored procedure name.

@parameter_name

Specifies the parameter for a procedure, as defined in the CREATE PROCEDURE statement. Parameter names must be preceded by the "at" symbol (@). When used with the @parameter_name = value form, parameter names and constants need not be supplied in the order defined in CREATE PROCEDURE statement. However, if the @parameter_name = value form is used for any parameter, it must be used for all subsequent parameters.

value

Is the value of the parameter to the procedure. If you do not use a parameter name, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.

@variable

Specifies the variable that stores a parameter value.

OUTPUT

Indicates that the stored procedure returns a parameter. The matching parameter in the stored procedure must also have been created with the keyword OUTPUT.

DEFAULT

Specifies that the default value is used.

Remarks

This statement allows you to run procedures on local or remote systems and store the returned information in a local table. The procedures can be system procedures, user-defined procedures, or extended procedures.

Specify the system and procedure you want to run by specifying the procedure name. To specify a remote system, the procedure name will begin with the server name followed by the database name, the owner of the procedure, and the procedure name. Each element of the procedure name is followed by a period except for the procedure name itself. For example, the remote procedure get_latest_sales owned by jake on the accounts server would look like this:

accounts.acct_db.jake.get_latest_sales
  

The local table is loaded with data returned from SELECT statements. The local table must already exist. Data returned from the PRINT statement, RAISERROR statement, FETCH statement, or other output is not stored in the table.

A procedure that returns multiple results sets appends the data to the table.

Data returned from the SELECT statement must conform to the datatypes in the existing local table's columns. For example, inserting varchar data into a tinyint data column is not permitted.

All INSERT statement rules apply to this operation. That is, table constraints, rules and triggers, and implicit datatype conversion rules are all enforced during table population. For more information about INSERT statement rules, see INSERT Statement.

For more information about writing procedures, see CREATE PROCEDURE Statement.

Errors are reported if there is a datatype mismatch between local table input columns and values returned from the procedure. Also, if the number of columns does not match the number of items returned in a row, an error is returned.

For more information about executing a procedure, see EXECUTE Statement in this document and in the Microsoft SQL Server Transact-SQL Reference.

Examples

A.    Populate Local Tables with Remote Database Information

This example retrieves information from a remote database for administration purposes. The human_resource.hr.dbo.fetch_employee_data and marketing.mis.dbo.fetch_sales_figures procedures pull data from remote sites into local tables.

INSERT INTO employee_info
EXECUTE human_resource.hr.dbo.fetch_employee_data
INSERT INTO sales_info
    EXECUTE marketing.mis.dbo.fetch_sales_figures 
  

B.    Assign the Current Value of the SET Option to a Variable

This example shows how to use the new INSERT EXECUTE feature to enable Transact-SQL code to assign the current value of a SET option to a variable.

SET NOCOUNT on
CREATE TABLE #tb_setopts
(SetOptName varchar(35) NOT NULL ,SetOptValue varchar(35) null)
INSERT INTO #tb_setopts (SetOptName,SetOptValue) 
EXEC('dbcc useroptions')
DECLARE @CurrentValue varchar(35)
SELECT @CurrentValue=case min(SetOptValue) WHEN 'SET' 
THEN 'on' else 'off' END
FROM #tb_setopts WHERE SetOptName='ansi_padding'
IF @CurrentValue='on' 
RAISERROR('The SET opt ''ansi_padding'' is''on''.' ,1,1)
ELSE
RAISERROR('The SET opt ''ansi_padding'' is''off''.',1,1)
IF object_id('tempdb..#tb_setopts') IS NOT NULL
DROP TABLE #tb_setopts

/********  Actual output....
DBCC execution completed. If DBCC printed error messages, see your
System Administrator.
The SET opt 'ansi_padding' is 'off'.
********/