What are the challenges you face when working across database platforms? Take the survey

T-SQL: Creating a (not quite) duplicate row


I've got a bit of an unusual request, I want to create some duplicate rows in a SQL Server table. We have a WorkOrders table that has our customer orders in it. Every order has a WOID (autoincrement key), a BlanketNumber (many WorkOrders can be under one Blanket for billing purposes) and a WorkOrderNumber and about 75 other fields. Due to the industry that we are in, it often occurs that a customer will request that an order is split between two destinations after they have placed it. I need to make a stored proc that will create a copy of a WorkOrder with the same BlanketNumber but a different WorkOrderNumber.

The main constraint I'm running into is that the fields in this table change relatively often, so I don't want to use an INSERT with a huge field list. I've been looking at using the INFORMATION_SCHEMA views to get a dynamic list of columns, but the performance of doing this field by field is not good.

I'd love to use INSERT INTO WorkOrders (SELECT * FROM WorkOrders WHERE...) but that won't work because of the keys and the fact that WorkOrderNumber must be unique.
Is there a way to do a SELECT ALL_FIELDS_EXECEPT(blah)? I've googled this a lot, but everything I look for pertains to removing duplicates, not creating them.
Anyone run into this problem before, or have a suggestion?


I didn't find the right solution from the internet.
Creative animation studio


Sign In or Register to comment.