need help with sql insert with concat

Question

Post reply

need help with sql insert with concat

Richard Cranston

Hall of Fame

Points: 3680
More actions
May 20, 2005 at 10:08 am

#167045

I need to insert data from a target table into a source table. The first column from both tables are the same (Product_ID); the second column of the source table needs to contain concat data from the source table.
I tried the following without success:
INSERT INTO tblCatalog ( Product_ID, img )
SELECT Product_Id, (CONCAT(Pattern,'-',Color,'.jpg'))
    FROM Product_Dimension
INSERT INTO tblCatalog ( Product_ID, (CONCAT(Pattern,'-',Color,'.jpg')) )
SELECT Product_Id,Pattern,Color
    FROM Product_Dimension
INSERT INTO tblCatalog
SET Product_ID=b.Product_ID, img= CONCAT(b.Pattern,'-',b.Color,'.jpg')
SELECT *
    FROM Product_Dimension b

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

INSERT INTO tblCatalog ( Product_ID, img )

SELECT Product_Id, Pattern + '-' + Color + '.jpg' as img

FROM dbo.Product_Dimension

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

BTW this assumes that Pattern and Color cannot contain nulls

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 3

great, that works just fine...thanks..

now the img has the following data:

"FSP -01 .jpg"

I need the data to be "FSP-01.jpg"

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 4

I think I found my anwser:

INSERT INTO tblCatalog ( Product_ID, img )

SELECT Product_Id, rtrim(Pattern) + '-' + rtrim(Color) + '.jpg' as img

FROM dbo.Product_Dimension

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Are the columns chars() or varchars()? If you use varchar instead of char then the trailing spaces will be removed automatically (or actually not added at all).

noeld SSC Guru Points: 96590 More actions · Answer 6

>> I think I found my anwser:

INSERT INTO tblCatalog ( Product_ID, img )

SELECT Product_Id, rtrim(Pattern) + '-' + rtrim(Color) + '.jpg' as img

FROM dbo.Product_Dimension <<

To be on the safe side:

INSERT INTO tblCatalog ( Product_ID, img )

SELECT Product_Id, ltrim(rtrim(Pattern)) + '-' + ltrim(rtrim(Color)) + '.jpg' as img

FROM dbo.Product_Dimension

* Noel