hoseam (2/10/2014)
@ChrisM@WorkThe end to end process is pretty much what I have said, I have products I need to clone. I have 10 tables, in any table I might need to clone a certain product. in each table there are products with their productID. If have productA in TableA and I need to clone this product, if productID of this product is 123, it's clone will have different productID, but everything else will be the same. but the products I need to clone are in different table.
So I'm thinking of a generic solution where the user can just pass the parameters then any table, row or column affected will be processed.
e.g @parameters, tablename, @productID, @newProductID, with these parameters, the proc or fuction should be able to clone any product in any table.
But other tables has IDENTITY(1,1) and other doesn't have. this comes a challenge, giving me errors I mentioned before.
10 product tables is quite unusual but I can see how it might arise when you contrast the data storage requirements of brassieres and shipping containers.
Personally, I'd go for a solution which doesn't look anything like your project at all - much simpler, if a little repetitive.
IF @ProductTable = 'Products01'
BEGIN
...
END
Over-engineered solutions such as the one you are suggesting are a pig to maintain and can take much longer than expected to write. Had you chosen the simpler option of ten conditional blocks of conventional TSQL (as opposed to dynamic sql), you wouldn't have needed any help and you would have finished coding and testing the same day.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden