INSERT INTO, EXCLUDING ONE COLUMN

Question

INSERT INTO, EXCLUDING ONE COLUMN

April 25, 2017 at 10:43 am

This was removed by the editor as SPAM
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

hoseam SSCertifiable Points: 5533 More actions · Answer 1

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.

hoseam SSCertifiable Points: 5533 More actions · Answer 2

@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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

hoseam (2/10/2014)
@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.

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Arno Ho SSCommitted Points: 1662 More actions · Answer 4

Arno Ho

SSCommitted

Points: 1662

April 25, 2017 at 11:58 pm

#1939869

Lovely solution !

hoseam SSCertifiable Points: 5533 More actions · Answer 5

hoseam

SSCertifiable

Points: 5533

April 26, 2017 at 12:01 am

#1939870

Very good Bernard, very good

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 6

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;

Chris Wooding SSCarpal Tunnel Points: 4550 More actions · Answer 7

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.