sql - 插入...值(SELECT...FROM...)




database syntax database-agnostic ansi-sql-92 (18)

对于Microsoft SQL Server,我会推荐学习解释MSDN上提供的SYNTAX。 使用Google,查找语法比以往任何时候都容易。

对于这种特殊情况,请尝试

Google:插入网站:microsoft.com

第一个结果将是http://msdn.microsoft.com/en-us/library/ms174335.aspx

如果发现很难解释页面顶部给出的语法,请向下滚动到示例(“使用SELECT和EXECUTE选项插入其他表中的数据”)。

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table       <<<<------- Look here ------------------------
        | execute_statement   <<<<------- Look here ------------------------
        | <dml_table_source>  <<<<------- Look here ------------------------
        | DEFAULT VALUES 
        }
    }
}
[;]

这应该适用于那里可用的任何其他RDBMS。 记住所有产品IMO的所有语法没有意义。

我正在尝试使用来自另一个表的输入INSERT INTO到表中。 尽管对于许多数据库引擎来说这是完全可行的,但我总是很难记住当天的SQL引擎( MySQLOracleSQL ServerInformixDB2 )的正确语法。

是否存在来自SQL standard (例如SQL-92 )的银牌语法,它可以让我插入值而不用担心底层数据库?


这对我有效:

insert into table1 select * from table2

这句话与甲骨文有点不同。


如果要使用SELECT * INTO表插入所有列,则可以尝试此操作。

SELECT  *
INTO    Table2
FROM    Table1;

如果使用INSERT VALUES路径插入多行,请确保使用圆括号将VALUES分隔到集合中,以便:

INSERT INTO `receiving_table`
  (id,
  first_name,
  last_name)
VALUES 
  (1002,'Charles','Babbage'),
  (1003,'George', 'Boole'),
  (1001,'Donald','Chamberlin'),
  (1004,'Alan','Turing'),
  (1005,'My','Widenius');

否则,MySQL对象的“列计数与第1行的值计数不匹配”,并且最终在最终找出如何处理它时写了一个简单的帖子。


我看到的两个答案在Informix中都很好地工作,基本上都是标准的SQL。 也就是说,符号:

INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...;

对于Informix以及我所期望的所有DBMS都能正常工作。 (5年或更长时间之前,这就是MySQL并不总是支持的东西;它现在对这种标准SQL语法有很好的支持,而AFAIK在这种表示法下可以正常工作)。列列表是可选的,但按顺序指示目标列,因此SELECT的结果的第一列将进入列出的第一列等。在没有列列表的情况下,SELECT结果的第一列进入目标表的第一列。

系统之间有什么不同可以用来识别不同数据库中的表格 - 该标准对于数据库间(更不用说数据库间管理系统)操作没有什么可说的。 使用Informix,可以使用以下表示法来标识表:

[dbase[@server]:][owner.]table

也就是说,您可以指定一个数据库,如果它不在当前服务器中,则可以选择标识承载该数据库的服务器,然后指定一个可选的所有者,点,最后是实际的表名称。 SQL标准针对Informix所谓的所有者使用术语模式。 因此,在Informix中,以下所有符号都可以标识一个表格:

table
"owner".table
dbase:table
dbase:owner.table
[email protected]:table
[email protected]:owner.table

一般所有者不需要被引用; 但是,如果您确实使用了引号,则需要正确拼写所有者名称 - 它会区分大小写。 那是:

someone.table
"someone".table
SOMEONE.table

全部识别相同的表格。 对于Informix,MODE ANSI数据库存在一个轻微的复杂性,其中所有者名称通常会转换为大写(informix是例外)。 也就是说,在一个MODE ANSI数据库(不常用)中,你可以写:

CREATE TABLE someone.table ( ... )

而系统目录中的所有者名称将是“某人”,而不是“某人”。 如果将所有者名称用双引号括起来,它就像分隔标识符一样。 使用标准SQL,可以在许多地方使用分隔标识符。 使用Informix,只能在所有者名称周围使用它们 - 在其他上下文中,Informix将单引号和双引号字符串视为字符串,而不是将单引号字符串分隔为字符串和双引号字符串作为分隔标识符。 (当然,为了完整起见,还有一个环境变量DELIMIDENT可以设置为任意值,但Y最安全 - 表示双引号总是围绕分隔标识符,单引号总是围绕字符串。)

请注意,MS SQL Server设法使用方括号中的[分隔标识符]。 它看起来很奇怪,当然不是SQL标准的一部分。


@ Shadow_x99 :应该可以正常工作,并且您还可以有多个列和其他数据:

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2
WHERE   table2.ID = 7;

编辑:我应该提到,我只在Access,SQL 2000/2005 / Express,MySQL和PostgreSQL中使用了这种语法,因此应该涵盖这些语法。 一位评论者指出,它将与SQLite3一起工作。


当表列序列已知时简单插入:

    Insert into Table1
    values(1,2,...)

简单的插入提及列:

    Insert into Table1(col2,col4)
    values(1,2)

当表(#table2)的选定列数等于插入表(Table1)时批量插入

    Insert into Table1 {Column sequence}
    Select * -- column sequence should be same.
       from #table2

当您只想插入到表格的期望列(表格1)时批量插入:

    Insert into Table1 (Column1,Column2 ....Desired Column from Table1)  
    Select Column1,Column2..desired column from #table2
       from #table2

INSERT INTO yourtable
SELECT fielda, fieldb, fieldc
FROM donortable;

这适用于所有DBMS


以下是如何从多个表格中插入。 这个特殊的例子是你在许多情况下有一个映射表的地方:

insert into StudentCourseMap (StudentId, CourseId) 
SELECT  Student.Id, Course.Id FROM Student, Course 
WHERE Student.Name = 'Paddy Murphy' AND Course.Name = 'Basket weaving for beginners'

(我认识到学生姓名上的匹配可能会返回多个值,但是您可以获得该想法。当Id是标识列并且未知时,匹配Id以外的内容是必要的。)


如果您要为SELECT部分中的所有列提供值,可以在不指定INSERT INTO部分中的列的情况下完成此操作。

比方说,table1有两列。 该查询应该可以工作:

INSERT INTO table1
SELECT  col1, col2
FROM    table2

这不会工作(没有指定col2值):

INSERT INTO table1
SELECT  col1
FROM    table2

我正在使用MS SQL Server。 我不知道其他RDMS的工作原理。


我其实更喜欢SQL Server 2008中的以下内容:

SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table2.Column2, 'Some String' AS SomeString, 8 AS SomeInt
INTO Table3
FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column3

它消除了添加Insert()集的步骤,并且您只需选择表中的哪些值。


插入FIRST_TABLE_NAME(COLUMN_NAME)SELECT COLUMN_NAME FROM ANOTHER_TABLE_NAME WHERE CONDITION;


  • INSERT INTO SELECT语句从一个表中复制数据并将其插入到另一个表中。

  • INSERT INTO SELECT要求源表和目标表中的数据类型匹配目标表中的现有记录不受影响

INSERT INTO SELECT语法: -

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

INSERT INTO SELECT示例: -

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

这是使用select使用值的另一个示例:

INSERT INTO table1(desc, id, email) 
SELECT "Hello World", 3, email FROM table2 WHERE ...

select *
into tmp
from orders

看起来不错,但只有在tmp不存在时才有效(创建并填充)。 (SQL服务器)

要插入到现有的tmp表中:

set identity_insert tmp on

insert tmp 
([OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]
      ,[RequiredDate]
      ,[ShippedDate]
      ,[ShipVia]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry] )
      select * from orders

set identity_insert tmp off

要从另一个表中获取多值INSERT中的一个值,我在SQLite3中执行了以下操作:

INSERT INTO column_1 ( val_1, val_from_other_table ) 
VALUES('val_1', (SELECT  val_2 FROM table_2 WHERE val_2 = something))

它简单,而不是INSERT查询的VALUES部分,只需使用SELECT查询如下。

INSERT INTO table1 ( column1 , 2, 3... ) SELECT col1, 2, 3... FROM table2

我正在使用PostgreSQL和我的层次结构的闭合表。 我有一个通用的存储过程为整个数据库:

CREATE FUNCTION nomen_tree() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
  old_parent INTEGER;
  new_parent INTEGER;
  id_nom INTEGER;
  txt_name TEXT;
BEGIN
-- TG_ARGV[0] = name of table with entities with PARENT-CHILD relationships (TBL_ORIG)
-- TG_ARGV[1] = name of helper table with ANCESTOR, CHILD, DEPTH information (TBL_TREE)
-- TG_ARGV[2] = name of the field in TBL_ORIG which is used for the PARENT-CHILD relationship (FLD_PARENT)
    IF TG_OP = 'INSERT' THEN
    EXECUTE 'INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT $1.id,$1.id,0 UNION ALL
      SELECT $1.id,ancestor_id,depth+1 FROM ' || TG_ARGV[1] || ' WHERE child_id=$1.' || TG_ARGV[2] USING NEW;
    ELSE                                                           
    -- EXECUTE does not support conditional statements inside
    EXECUTE 'SELECT $1.' || TG_ARGV[2] || ',$2.' || TG_ARGV[2] INTO old_parent,new_parent USING OLD,NEW;
    IF COALESCE(old_parent,0) <> COALESCE(new_parent,0) THEN
      EXECUTE '
      -- prevent cycles in the tree
      UPDATE ' || TG_ARGV[0] || ' SET ' || TG_ARGV[2] || ' = $1.' || TG_ARGV[2]
        || ' WHERE id=$2.' || TG_ARGV[2] || ' AND EXISTS(SELECT 1 FROM '
        || TG_ARGV[1] || ' WHERE child_id=$2.' || TG_ARGV[2] || ' AND ancestor_id=$2.id);
      -- first remove edges between all old parents of node and its descendants
      DELETE FROM ' || TG_ARGV[1] || ' WHERE child_id IN
        (SELECT child_id FROM ' || TG_ARGV[1] || ' WHERE ancestor_id = $1.id)
        AND ancestor_id IN
        (SELECT ancestor_id FROM ' || TG_ARGV[1] || ' WHERE child_id = $1.id AND ancestor_id <> $1.id);
      -- then add edges for all new parents ...
      INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT child_id,ancestor_id,d_c+d_a FROM
        (SELECT child_id,depth AS d_c FROM ' || TG_ARGV[1] || ' WHERE ancestor_id=$2.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+1 AS d_a FROM ' || TG_ARGV[1] || ' WHERE child_id=$2.' 
        || TG_ARGV[2] || ') AS parent;' USING OLD, NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$_$;

然后对于我有层次结构的每个表,我创建一个触发器

CREATE TRIGGER nomenclature_tree_tr AFTER INSERT OR UPDATE ON nomenclature FOR EACH ROW EXECUTE PROCEDURE nomen_tree('my_db.nomenclature', 'my_db.nom_helper', 'parent_id');

为了填充现有层次结构中的闭包表,我使用这个存储过程:

CREATE FUNCTION rebuild_tree(tbl_base text, tbl_closure text, fld_parent text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    EXECUTE 'TRUNCATE ' || tbl_closure || ';
    INSERT INTO ' || tbl_closure || ' (child_id,ancestor_id,depth) 
        WITH RECURSIVE tree AS
      (
        SELECT id AS child_id,id AS ancestor_id,0 AS depth FROM ' || tbl_base || '
        UNION ALL 
        SELECT t.id,ancestor_id,depth+1 FROM ' || tbl_base || ' AS t
        JOIN tree ON child_id = ' || fld_parent || '
      )
      SELECT * FROM tree;';
END;
$$;

闭包表用3列定义 - ANCESTOR_ID,DESCENDANT_ID,深度。 有可能(甚至建议)为ANCESTOR和DESCENDANT存储具有相同值的记录,深度值为零。 这将简化对层次结构进行检索的查询。 他们确实很简单:

-- get all descendants
SELECT tbl_orig.*,depth FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth <> 0;
-- get only direct descendants
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth = 1;
-- get all ancestors
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON ancestor_id = tbl_orig.id WHERE descendant_id = XXX AND depth <> 0;
-- find the deepest level of children
SELECT MAX(depth) FROM tbl_closure WHERE ancestor_id = XXX;




sql database syntax database-agnostic ansi-sql-92