Is there any way to simplify a verbose SQL INSERT INTO(..) query?

Question

I have a table with one id (autonumber) field and 50 other fields. The table is normalized, these are 50 material properties etc.

I want to copy a record from this table into the same table - only the autoincrement id will be different. The query I am using now is

INSERT INTO tableName (field1,field2,....field50)
SELECT field1,field2,....field50 from tableName
WHERE autoid=1234;

Note that I have to type in ALL the 50 field names, twice! Is there any way to shorten this query so I don't have to type all of them?

Thanks.

Answer 1

You could say:

INSERT INTO tableName
SELECT * from tableName WHERE autoid=1234;

Answer 2

INSERT INTO tableName
SELECT * from tableName 
WHERE autoid=1234;

If you have 50 columns in a table, maybe you should rethink about your database design?

Answer 3

Looking at the above answers, the main issue is that you will get a primary key violation if you do insert into <tableName> select * from <tableName>

Here is my guess at a possible solution:

SELECT *
INTO #tempTable
FROM TableName
WHERE autoid = 1234

this gets your values. then you need to get rid of the ID

ALTER TABLE #tempTable
DROP COLUMN autoid

Then simply insert this back into the main table

INSERT INTO tableName
SELECT * FROM #tempTable

i'm not a gun at SqlServer, mainly having worked with sybase so there might be some syntactic issues. Please feel free to correct me, i will alter my answer