Insert without column name listing
-
Hi guys,
I want to duplicate a row in a table without listing all the column names.
Somthing like :
"insert into Employees
select * from Employees
where EmployeeID = 1"
Keep in mind that 'EmployeeID' is Identity column.
I won't take 'no can do' for an answer .....
Thanks
Yaniv
-
I assume that you want the new employeeID to be different - fair assumption.
In that case, with standard and straightforward SQL, no can do unfortunately...
Only way around it would be to have some dynamic SQL loop through the columns of the table and automatically build up a select / insert statement for you. This would involve having a cursor go over the information_schema.columns table - it would need to skip the identity column... Messy.
Why can't you list the column names. If it is because you cannot be bothered typing them, use SQL Query Analyser - you can drag the column names as a group or one at a time into the query window. On the other hand, if it is because your app adds and removes columns from the table (like user-defined fields) then you could
a) Consider a different way of storing the fields where you don't have to modify the table (store them in a 1:many relationship in another table or I have also seen ppl store them in a SQL 2005 XML column - don't like that but might work for you)
b) Have your app also update a standard stored proc which contains your insert statement. This way you can just call the stored proc knowing that it will correctly do the insert.
Hope that helps
-
Hi Ian,
The main reason for not 'wanting' to list all column names is :
'app adds and removes columns from the table'.
True, I'm lazy but not that laze ....
Anyway, I allready used INFORMATION_SCHEMA. I was hoping for another solution.
I'll accept your 'no can do' .... just this one time .....
-
select * into #temp from Employees
alter table #temp drop column EmployeeID
insert into Employees
select * from #temp
I havnt tested and it is may need to be addapted but with a bit of fiddling it should work. Obviously it also relies on you having a consistent naming convention for you ID column.
-
I versions before 9, I think an insert into a table that has an identity column has to have a column list:
- insert tbl1(col1,col2)
- select 1,2
So Jules's answer can only work in v9.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply