SQL Server : INSERT from one table to another table

Question

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

Example:

Table 1:

     emp1:
          Name    char 50
          Age     int
          Salary  Float

Table 2:

     emp2:
          Name    char 50
          Salary  Float 
          Age     int

My code:

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.

You have to specify columns

SouXin
– SouXin

2017-06-09 17:40:22 +00:00
Commented Jun 9, 2017 at 17:40 — SouXin
– SouXin, Commented Jun 9, 2017 at 17:40

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

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.

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:

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.

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 9, 2017 at 17:43

SouXin

1,57411 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

You can, and a best practice says you always should!

SouXin Over a year ago

@marc_s. =) It's why in comment I mentioned about 'have to'

Hamid Ghasemi · Accepted Answer · 2017-06-09 17:41:45Z

0

test this query , it should work:

insert into emp1 select Name, Age, Salary from emp2;

and I suggest you to read this tutorial.

answered Jun 9, 2017 at 17:41

Hamid Ghasemi

8924 gold badges14 silver badges34 bronze badges

Comments

Enrico · Accepted Answer · 2019-05-24 09:50:50Z

0

It's simple just reorder columns during insert:

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

answered May 24, 2019 at 9:50

Enrico

1412 silver badges9 bronze badges

1 Comment

Mahesh Y Over a year ago

if its more than 100 fields , its tedious.

Collectives™ on Stack Overflow

SQL Server : INSERT from one table to another table

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related