Adds data into an existing table on the federated system.

Syntax

INSERT INTO tableName [ ( colList ) ] { ( VALUES valuesList ) | selectQuery };

Arguments

  • tableName: The fully qualified name or simple name of the destination table. This table must already exist at the specified location within the federated system, and be available for inserts.

  • colList: A comma-separated list of columns in tableName.

  • If data will be inserted for all columns, this clause can be omitted.

  • If a colList is provided, the number of values must exactly match the number of values in valuesList.

  • If a row is inserted and a column with a default value is excluded from colList, the added row will be added with the default value for the column.

  • valuesList: A comma-separated list of values, matching the number, data type, and order of values in colList.

  • If colList is not included, valuesList must match the number, data type, and order of values in tableName.

  • valuesList can contain explicit CAST statements, when needed. If data does not match the data type of the destination column, the data will be passed to the target system as a string, for implicit conversion.

  • The valuesList can include information to insert multiple rows; include multiple, consecutive lists of data matching the colList.

  • selectQuery: A query returning values for insert into the table.

  • The selectList of the query must match the number, data type, and order of values in colList.

  • If selectQuery returns multiple rows, the INSERT statement will insert one row to tableName for each row in the selectQuery data set.

Additional Information

Examples


--A simple INSERT command using a specified destination column list and the VALUES expression.

INSERT INTO SQLServer02.dbo.inventory.customer
( custAdd, custCity, custState, custZip )
VALUES ('1234 Main Street', 'Anywhere', 'CA', 90000);

--A simple INSERT command without destination columns and including a SELECT query

INSERT INTO table1 SELECT * FROM table2;