Inserting row into SQL table with some predefined values, and some from another table as well as join string.

Question

Hi I am trying to run this query below:

INSERT INTO Database2.Table2 ('1', getdate(), getdate(), '',ID,'','0','0','0','',ID,'','',getdate(),getdate(),'','0','"stackoverflow.com/"'+'ID','0','product','','0') 
SELECT ID
FROM Database1.Table1;

I am inserting a new row with constant data mixed with data from another table, this case Database1.Table1 "ID". I constantly keep getting a select statement error when I try to run this code. Is there something I am overseeing or is this statement all wrong? Thank You

Do you really need both single and double quotes around stackoverflow.com/? And why are you quoting ID after that? That's the same as just writing '"stackoverflow.com/"ID'. Is that ID supposed to be unquoted, so it gets it from Table1? — Barmar
– Barmar, Commented Oct 11, 2018 at 2:45

Barmar · Accepted Answer · 2018-10-11 02:44:24Z

1

Put the constants into the SELECT list.

INSERT INTO Database2.Table2 
SELECT '1', getdate(), getdate(), '',ID,'','0','0','0','',ID,'','',getdate(),getdate(),
       '','0',CONCAT('"stackoverflow.com/"','ID'),'0','product','','0', ID
FROM Database1.Table1;

BTW, I strongly recommend you get out of the habit of using INSERT without listing the column names before the values. Depending on the specific order of columns in the table definition is very error-prone.

Also, MySQL doesn't use + for string concatenation by default, it uses the CONCAT() function.

edited Oct 11, 2018 at 2:44

answered Oct 11, 2018 at 2:37

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

But MySQL can use the ANSI concatenation operator ||, if a certain flag is set +1.

Barmar Over a year ago

@TimBiegeleisen I added "by default", since that flag is probably rarely set.

Collectives™ on Stack Overflow

Inserting row into SQL table with some predefined values, and some from another table as well as join string.

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related