T-SQL: Creating a (not quite) duplicate row
seanjsutin
Posts: 2 New member
in SQL Clone
Hello,
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?
Thanks
I didn't find the right solution from the internet.
References:
https://arstechnica.com/civis/viewtopic.php=359051
Creative animation studio
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?
Thanks
I didn't find the right solution from the internet.
References:
https://arstechnica.com/civis/viewtopic.php=359051
Creative animation studio
Tagged:
Answers
Probably the best place to ask would be the SQL Server Central forums - this is the forum for our SQL Clone product.
Owen
Redgate Software