Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.20.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Insert</title> <link rel="StyleSheet" href="css/sqlsupport.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="sqlsupport.12.01.html#71188">10 SQL Statements and Extensions for the Salesforce Driver</a> : Insert</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="71188">Insert</a></div> <div class="Body"><a name="89272">The Insert statement is used to add new rows to a table. You can specify either of the </a>following options:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="89273">List of values to be inserted as a new row</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="89274">Select statement that copies data from another table to be inserted as a set of new rows</a></div> </td> </tr> </table> </div> <div class="gutter_text_right"><a name="89290">Grammar</a></div> <div class="syntax_first"><a name="89291">INSERT INTO table_name [(column_name[,column_name]...)] </a></div> <div class="syntax"><a name="89292">{VALUES (</a><span class="EquationVariables">expression</span><span style="font-style: italic;"> </span>[,<span class="EquationVariables">expression</span>]...) | <span class="EquationVariables">select_statement</span>}</div> <div class="Body"><span class="EquationVariables"><a name="89299">table_name</a></span> is the name of the table in which you want to insert rows. </div> <div class="Body"><span class="EquationVariables"><a name="89300">column_name</a></span> is optional and specifies an existing column. Multiple column names (a column list) must be separated by commas. A column list provides the name and order of the columns, the values of which are specified in the Values clause. If you omit a <span class="EquationVariables">column_name</span> or a column list, the value expressions must provide values for all columns defined in the table and must be in the same order that the columns are defined for the table. Table columns that do not appear in the column list are populated with the default value, or with NULL if no default value is specified. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.20.html#199267', '');">â??Specifying an External ID Columnâ??</a></span> for more information.</div> <div class="Body"><span class="EquationVariables"><a name="89301">expression</a></span> is the list of expressions that provides the values for the columns of the new record. Typically, the expressions are constant values for the columns. Character string values must be enclosed in single quotation marks (â??). See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.27.html#95580', '');">â??Literalsâ??</a></span> for more information.</div> <div class="Body"><span class="EquationVariables"><a name="89642">select_statement</a></span> is a query that returns values for each <span class="EquationVariables">column_name</span> value specified in the column list. Using a Select statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single Insert statement. The Select statement is evaluated before any values are inserted. This query cannot be made on the table into which values are inserted. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#107999', '');">â??Selectâ??</a></span> for information about Select statements.</div> <div class="head_2_no_TOC"><a name="199267">Specifying an External ID Column</a></div> <div class="Body"><a name="199292">To specify an external ID column to look up the value of a foreign key column, use the </a>following syntax:</div> <div class="syntax_first"><span class="EquationVariables"><a name="199287">column_name</a></span> EXT_ID [<span class="EquationVariables">schema_name</span>.[<span class="EquationVariables">table_name</span>.] ]<span class="EquationVariables">ext_id_column</span></div> <div class="Body"><a name="199330">where:</a></div> <div class="Body"><span class="Syntax"><a name="199420">EXT_ID</a></span> is used to specify that the column specified by <span class="EquationVariables">ext_id_column</span> is used to look up the rowid to be inserted into the column specified by <span class="EquationVariables">column_name</span>.</div> <div class="Body"><span class="EquationVariables"><a name="199389">schema_name</a></span> is the name of the schema of the table that contains the foreign key column being specified as the external ID column.</div> <div class="Body"><span class="EquationVariables"><a name="199341">table_name</a></span> is the name of the table that contains the foreign key column being specified as the external ID column.</div> <div class="Body"><span class="EquationVariables"><a name="199342">ext_id_column</a></span> is the external ID column.</div> <div class="gutter_text_right"><a name="89306">Examples</a></div> <div class="Body"><span class="Action_bold"><a name="89622">Example A</a></span></div> <div class="Body"><a name="143859">The following example uses a list of expressions to insert records. Each Insert statement </a>adds one record to the database table. In this case, one record is added to the table <span class="Syntax">emp</span>. Values are specified for five columns. The remaining columns in the table are assigned the default value or NULL if no default value is specified.</div> <div class="syntax_first"><a name="89623">INSERT INTO emp (last_name, </a></div> <div class="syntax"><a name="149565"> first_name, </a></div> <div class="syntax"><a name="149566"> emp_id, </a></div> <div class="syntax"><a name="149567"> salary, </a></div> <div class="syntax"><a name="177554"> hire_date)</a></div> <div class="syntax"><a name="177555">VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06})</a></div> <div class="hanging_label_head"><span class="Action_bold"><a name="177556">Example B</a></span></div> <div class="Body"><a name="89616">The following example uses a Select statement</a> to insert records. The number of columns in the result of the Select statement must match exactly the number of columns in the table if no column list is specified, or it must match the number of column names specified in the column list. A new entry is created in the table for every row of the Select result.</div> <div class="syntax_first"><a name="89308">INSERT INTO emp1 (first_name, </a></div> <div class="syntax"><a name="149579"> last_name, </a></div> <div class="syntax"><a name="149580"> emp_id, </a></div> <div class="syntax"><a name="149581"> dept, </a></div> <div class="syntax"><a name="149587"> salary)</a></div> <div class="syntax"><a name="89309">SELECT first_name, last_name, emp_id, dept, salary FROM emp</a></div> <div class="syntax"><a name="89310">WHERE dept = 'D050'</a></div> <div class="hanging_label_head"><a name="199001">Example C</a></div> <div class="Body"><a name="199078">The following example uses a list of expressions to insert records and specifies an </a>external ID column (a foreign key column) named <span class="Syntax">accountId</span> that references a table that has an external ID column named <span class="Syntax">AccountNum</span>.</div> <div class="syntax_first"><a name="199170">INSERT INTO emp (last_name, </a></div> <div class="syntax"><a name="199171"> first_name, </a></div> <div class="syntax"><a name="199172"> emp_id, </a></div> <div class="syntax"><a name="199173"> salary, </a></div> <div class="syntax"><a name="199174"> hire_date,</a></div> <div class="syntax"><a name="199182"> accountId EXT_ID AccountNum)</a></div> <div class="syntax"><a name="199175">VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06}, 0001)</a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de