CODEWITHSUNDEEP
sqlsql-server
PyDeveloper
PyDeveloper

Reputation: 319

INSERT - SELECT * instead of one column

Looking for easy way to insert select column values :

Example

TableA    TABLEB
-----------------
Field1    Field1
Field2    Field2
Field3    Field3
Field4    Field4
Field5    Field5
          FleldX

My tables contain around 80 columns. Now I want just to copy data from one table to another, like:

Insert Into table B (Field1, Field2, Field3, Field4, Field5, FieldX) 
VALUES SELECT (Field1, Field2, Field3, Field4, Field5, 'HARDCODEVALUE') 
       FROM TableA

Do I need to set explicit all 80 column names, or the best solution would be if it map fields automatically so I can set like this

Insert Into table B (Field1, Field2, Field3, Field4, Field5, FieldX) 
VALUES
    SELECT (Field4, Field3, Field2, Field1, Field5, 'HARDCODEVALUE' AS FieldX) 
    FROM TableA

Where I can reorder column names and SQL do other for me?

Which way is best to use ?

Upvotes: 1

Views: 3729

Answers (1)

HLGEM
HLGEM

Reputation: 96552

Ok first your syntax will not work no matter what. Try

INSERT INTO tableB (Field1, Field2, Field3, Field4, Field5, FieldX) 
SELECT Field4, Field3, Field2, Field1, Field5, 'HARDCODEVALUE' AS FieldX 
FROM TableA

Next yes you should ALWAYS use all of the columns in the order you want to insert them into the table both in the select and insert. To do otherwise is a very big SQL antipattern and it will cause problems down the line especially if columns are changing. You always want the right column to match to the right column. This becomes even more problematic if the field names are not always direct matches.

If your table structure changes structure daily, then you have a design problem as that should never happen.

I could suggest you use the informationschema tables to find the columns on the fly, but really if tableA adds a column, how do you know which column to map it to in tableB? If a column is deleted from tableB, how do you know to remove which column to remove from TableA in the SELECT? Let me illustrate the problem.

Suppose you have this structure:

TABLEA
USERID
USERLOGIN
USERPASSWORD

TABLEB
USERID
SYSLOGIN
SYSPASSWORD
USERTYPE

Then looking at the SELECT * insert

INSERT INTO tableB 
SELECT *, 'User' AS USERTYPE
FROM TableA

Which is the equivalent of

INSERT INTO tableB (USERID,SYSLOGIN,SYSPASSWORD,USERTYPE) 
SELECT USERID,USERLOGIN,USERPASSWORD, 'User' AS USERTYPE
FROM TableA

At this point everything is fine. But what happens when the structure changes?

Then the structure becomes

TABLEA
USERID
USERLOGIN
USERPASSWORD
LASTLOGINDATE

TABLEB
USERID
SYSLOGIN
SYSPASSWORD
USERTYPE
LASTLOGINDATE

If you use select *

INSERT INTO tableB 
SELECT *
FROM TableA

is the equivalent of 

INSERT INTO tableB (USERID,SYSLOGIN,SYSPASSWORD,USERTYPE, LASTLOGINDATE) 
SELECT USERID,USERLOGIN,USERPASSWORD, LASTLOGINDATE,'User' AS USERTYPE
FROM TableA

As you can see, by using SELECT *, you end up with LASTLOGINDATE trying to be inserted into USERTYPE and the value you previously hardcoded USERTYPE going into LASTLOGINDATE. This is because when you don't specify the columns, SQL server will take them in order. If the last two columns are different incompatible data types then the query will fail. It is worse if they are compatible datatypes because the query will succeed and you will have bad data in your database.

Other problems that can arise are that when you don't specify the column names, you need to have the exact same number of columns for the insert to work. If you add a column to one table and not the other, you will have problems. If you remove a column from the middle of the table and add a column, you could end up changing which column several of the columns are mapped to which will either cause an error if the datatypes are not compatible or worse, insert bad data. The inserting bad data is especially bad because you may not find out it is happening for weeks or even months.

Upvotes: 3

Related Questions