February 10, 2014 at 3:33 am
@ChrisM@Work
The 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.
February 10, 2014 at 3:38 am
@a.guillaume
I'm trying to shy away from creating a View or Table for that matter specifying the columns, because if there are any changes to a Table I will need to change a View as well.
hence:
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity = 0
ORDER BY column_id;
this code is the same as creating a #temp table, any changes to my tables won't impact the procedure.
February 12, 2014 at 2:58 am
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
April 25, 2017 at 10:43 am
This was removed by the editor as SPAM
April 25, 2017 at 11:58 pm
Lovely solution !
April 26, 2017 at 5:53 am
Here is a way to generate the list of all columns in the table that are not identity. Needs some modification for the script. You can then use the list twice in the insert statement. Sorry, I can't figure out how the SQL Code button works.
declare @cols1 nvarchar(max)
set @cols1 = stuff((select distinct ', ' + quotename(name)
from sys.columns
where object_name(object_id) = 'TBL1'
and is_identity = 0
for XML path('')), 1, 2,'');
select @cols1;
April 26, 2017 at 9:29 am
Why do you want to exclude the IDENTITY columns in the first place? If you use SET IDENTITY_INSERT <your table> ON you can copy the identity values across as well.
April 26, 2017 at 9:35 am
This was removed by the editor as SPAM
April 26, 2017 at 10:12 am
This was removed by the editor as SPAM
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply