Copy data into same table with different value for some columns

Question

I know that I can copy the rows into the same table while specifying different values for columns that need to contain different values by actually writing ALL the column names in the query like so:

INSERT INTO 
    my_table (col1, col2, col3, col4) 
SELECT 
    col1, 
    col2, 
    [value_for_col_3], 
    col4 
FROM 
    my_table;

WHERE [value_for_col_3] is the raw value that I want for the column col3. This works fine, but in cases where my table contains a lot of columns, it would be cumbersome to write all the column names. Is there a way to perform the same operation without typing all the column names of my table (while still being able to specify different values for certain columns)?

Thanks in advance for any help.

You need to list each column individually. You could duplicate the columns using `*`, but that probably wouldn't help you. — Gordon Linoff, Oct 13 '15 at 15:15
you could write a dynamic query but that would be cumbersome — JamieD77, Oct 13 '15 at 15:31
Nope, not syntactically. However, if you just don't want to type and don't actually mind the query being long, MySQL Workbench (and I am sure other management programs) can create standard verbose SELECT and INSERT query strings just by right-clicking on the table you want the query for. — Uueerdo, Oct 13 '15 at 15:47

score 0 · Answer 1 · edited May 23 '17 at 12:14

In this answer, I'm giving a basic outline instead of writing out all of the code since you're looking to see if what you want can be done. This is a possbile method of doing so.

The method is to get the list of all of the names of the columns except for the ones you don't want, then use the output of those column names in a query.

This shows how to select all but certain columns in SQL Server, but I'm sure syntax could be modified to work in MySQL.

(Copy and pasting the below code from one of the answers here: SQL exclude a column using SELECT * [except columnA] FROM tableA?)

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'MyTable'
            )
            and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from MyTable where';  
EXEC (@query);

Just the SELECT statement,

SELECT @query = 'select ' + @cols + ' from MyTable where';

would be modified to your insert into statement.

Copy data into same table with different value for some columns

1 Answers1