SQL Server:从一个表插入到另一个表 [英] SQL Server : INSERT from one table to another table

问题描述

我在将数据从一个表插入到另一个表时遇到问题，表结构相同，但列位置不同.

I am facing a problem regarding inserting data from one table to another table, with the same table structure, but with different column positions.

示例:

表 1:

     emp1:
          Name    char 50
          Age     int
          Salary  Float

表 2:

     emp2:
          Name    char 50
          Salary  Float 
          Age     int

我的代码:

insert into emp1
select * from emp2

我无法插入，因为一个表与另一个表的列顺序不同，但是，两个表具有相同的名称和数据类型.

I can't insert because the column order is different from one table to the other, but, both tables have same name and data types.

推荐答案

您可以(或如@marc_s 提到的应该)指定列.

You can (or as @marc_s mentioned should) specify columns.

这样做您可以更改这些表的结构(可能发生，对吧?)而不会产生任何后果(除非您删除这些列).您的代码仍然有效.

Doing that you can change a structure of these tables (could happen, right?) without any consequences (except if you delete these columns). Your code still working.

此外，指定列名称对任何人来说都更具可读性.您不需要调用 sp_help 或任何其他命令来检查结构(表架构).

Moreover specifying columns name it's just more readable for anybody. You don't need to call sp_help or any other commands to check the structure (table schema).

顺便说一句，如果不只指定要查找的列，那么在两个表或任何表中都有主键将引发异常:

BTW having a primary key in both tables or any table will throw an exception if not specifying the sought columns only:

如果 INSERT 语句违反约束或规则，或者它的值与列的数据类型不兼容，则语句失败并返回错误消息.

If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and an error message is returned.

如果 INSERT 使用 SELECT 或 EXECUTE 加载多行，则加载值发生的任何违反规则或约束的行为都会导致语句停止，并且不会加载任何行.

If INSERT is loading multiple rows with SELECT or EXECUTE, any violation of a rule or constraint that occurs from the values being loaded causes the statement to be stopped, and no rows are loaded.

    INSERT INTO emp1 (Name
        ,Age 
        ,Salary )
    SELECT Name
        ,Age 
        ,Salary
    FROM emp2

这篇关于SQL Server:从一个表插入到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！