How to copy data from one table to another "EXCEPT" one field

Question

How to INSERT into another table except specific field

e.g

TABLE A

ID(auto_inc)    CODE    NAME
1               001     TEST1
2               002     TEST2

I want to insert CODE and NAME to another table, in this case TABLE B but except ID because it is auto increment

Note: I don't want to use "INSERT INTO TABLE B SELECT CODE, NAME FROM TABLE A", because I have an existing table with around 50 fields and I don't want to write it one by one

Thanks for any suggests and replies

Just list the columns. You can get them from `information_schema.columns` if you don't want to type them. — Gordon Linoff, Sep 11 '14 at 03:37
You cannot `select *` minus the first column, you have to write the columns out. Depending on your needs, you could possibly use dynamic sql, but I'd say to map your columns. — sgeddes, Sep 11 '14 at 03:37
if going into an existing table are these inserts only? (i.e. you don't want to update some existing records with this data?) — Paul Maxwell, Sep 11 '14 at 03:44

score 2 · Answer 1 · answered Sep 11 '14 at 04:16

You can get all the columns using information_schema.columns:

select group_concat(column_name separator ', ')
from information_schema.columns c
where table_name = 'tableA' and
      column_name <> 'id';

This gives you the list. Then past the list into your code. You can also use a prepared statement for this, but a prepared statement might be overkill.

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

This can't be done without specifying the columns (excludes the primary key).

This question might help you. Copy data into another table

edited May 23 '17 at 11:44

Community

1
1

answered Sep 11 '14 at 03:48

CharlesX

128
1
7

score 1 · Answer 3 · answered Sep 11 '14 at 07:40

1

If this is a one time thing? If yes, do the insert into tableA (select * from table B) then Alter the table to drop the column that your dont need.

answered Sep 11 '14 at 07:40

mj527

161
3
8

that's thinking "outside the box"; occasionally I have trouble with the alter table mechanism, however.... – JosephDoggie Oct 08 '15 at 19:10

Coolbuck · Answer 4 · 2014-12-26T23:46:01.220

I tried to copy from a table to another one with one extra field.
source table is TERRITORY_t * the principle is to create a temp table identical to the source table, adjust column fields of the temp table and copy the content of the temp table to the destination table.

This is what I did:

create a temp table called TERRITORY_temp

generate SQL by running export

CREATE TABLE IF NOT EXISTS TERRITORY_temp ( Territory_Id int(11) NOT NULL, Territory_Name varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (Territory_Id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

copy over with INSERT INTO TERRITORY_temp (Territory_Id, Territory_Name) VALUES (1, 'SouthEast'), (2, 'SouthWest'), (3, 'NorthEast'), (4, 'NorthWest'), (5, 'Central');

or INSERT INTO TERRITORY_temp SELECT * from TERRITORY_t

add the extra field(s) to match with the new table
copy from the temp table to the destination table

INSERT INTO TERRITORY_new SELECT * from TERRITORY_temp

Please provide feedback.

That's now what the OP asked for. They want to skip the ID from the original table. The proper approach is to list out the columns from the information_schema and use this in the select clause. — Born2Code, Dec 26 '14 at 20:28
Your answer will be much more helpful if you convert *your* use case into the OP's use case, as well as using code formatting. This will make your snippet easier to understand. — Danny Bullis, Jan 29 '19 at 17:49

Marius D · Answer 5 · 2021-01-28T09:21:25.120

Step 1. Create stored procedure

CREATE PROCEDURE CopyDataTable

    @SourceTable varchar(255),
    @TargetTable varchar(255),
    @SourceFilter nvarchar(max) = ''

AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @SourceColumns VARCHAR(MAX)=''
    DECLARE @TargetColumns VARCHAR(MAX)=''
    DECLARE @Query VARCHAR(MAX)=''

    SELECT 
           @SourceColumns = ISNULL(@SourceColumns +',', '') + T.COLUMN_NAME
    FROM 
    ( 
        select name as COLUMN_NAME from sys.all_columns
        where object_id = (select object_id from sys.tables where name = @SourceTable)
        and is_identity = 0
    )T

    SELECT 
           @TargetColumns = ISNULL(@TargetColumns +',', '') + T.COLUMN_NAME
    FROM 
    ( 
        select name as COLUMN_NAME from sys.all_columns
        where object_id = (select object_id from sys.tables where name = @TargetTable)
        and is_identity = 0
    )T


    set @Query = 'INSERT INTO ' + @TargetTable + ' (' + SUBSTRING(@TargetColumns,2 , 9999) + ') SELECT ' + SUBSTRING(@SourceColumns,2 , 9999) + ' FROM ' + @SourceTable + ' ' + @SourceFilter;

    PRINT @Query
    --EXEC(@Query)
    
END
GO

Step 2. Run stored procedure

use YourDatabaseName exec dbo.CopyDataTable 'SourceTable','TargetTable'
Explanations

a) dbo.CopyDataTable will transfer all data from SourceTable to TargetTable, except field with Identity

b) You can apply filter when call stored procedure, in order to transfer only row based on criteria

exec dbo.CopyDataTable 'SourceTable','TargetTable', 'WHERE FieldName=3'

exec dbo.CopyDataTable 'SourceTable','TargetTable', 'WHERE FieldName=''TextValue'''

c) Remove -- from --EXEC(@Query) WHEN finish

How to copy data from one table to another "EXCEPT" one field

5 Answers5