can I insert a copy of a row from table T into table T without listing its columns and without primary key error?

I want开发者_如何学C to do something like this:

INSERT INTO T SELECT * FROM T WHERE Column1 = 'MagicValue' -- (multiple rows may be affected)

The problem is that T has a primary key column and so this causes an error as if trying to set the primary key. And frankly, I don't want to set the primary key either. I want to create entirely new rows with new primary keys but the rest of the fields being copied over from the original rows.

This is supposed to be generic code applicable to various tables. Well, so if there is no nice way of doing this, I will just write code to dynamically extract column names, construct the list etc. But maybe there is? Am I the first guy trying to create duplicate rows in a database or something?

I'm assuming by "Primary Key" you mean identity or guid data types that auto-assign or auto-increment.

Without some very fancy dynamic SQL, you can't do what you are after. If you want to insert everything but the identity field, you need to specify fields.

If you want to specify a value for that field, you need to specify all the fields in the SELECT and in the INSERT AND turn on IDENTITY_INSERT.

You don't gain anything from duplicating a row in a database (considering you didn't try to set the Primary Key). It would be wiser and will avoid problem to have another column called "amount" or something.

something like

UPDATE T SET Amount = Amount + 1 WHERE Column1 = 'MagicValue'

or if it can increase by more than 1 like amount of returned fields

Update T SET Amount = Amount * 2 WHERE Column1 = 'MagicValue'

I'm not sure what you're trying to do exactly but if the above doesn't work for what you're doing I think your design requires a new table and insert it there.

EDIT: Also as mentioned under your comments, a generic insert doesn't really make sense. Imagine, for this to work, you need the same number of fields, and they will hold the same values suggesting that they should also have the same names(even if it wouldn't require it to). It would basically be the same table structure twice.