INSERT into table A based on SELECT from table B

[Former Member]

I've been scratching my head (and a few other parts) for a while - maybe still brain dead from the clock forward thing 🙂

Need to insert values where one of the fields is an ID number selected from another table, a bit like this

(SELECT Customer_ID FROM Customers WHERE Cust_Name LIKE '%Unique String%') AS Cust_ID

INSERT INTO A_Sales_Ledger(xxx, yyyy, Customer_ID) VALUES (yyyy, yyyy, Cust_ID)

So the select will return, say, 65 records and it will insert 65 new rows into A_Sales_Ledger with the corresponding Customer_ID

Thanks

[Former Member · Accepted solution]

Hello Dave,

Turn it on its head and massage the syntax a little bit......

INSERT INTO A_Sales_Ledger(xxx, yyyy, Customer_ID)

SELECT xxx, yyy, Customer_ID FROM Customers WHERE Cust_Name LIKE '%Unique String%';

DocCommentXchange  - Syntax 2.

HTH, Paul

[Former Member · Accepted solution]

Hey Paul

Thank you - think I have been standing on my head today!

Just to confirm (don't mind hitting my head for a while with SELECT errors, but if an INSERT goes pear shaped it could have some undesirable consequences)

I don't need VALUES?

[Former Member · Accepted solution]

Hello Dave,

No, you don't need VALUES if you use the INSERT...SELECT.. syntax as the inserting values are provided from the select. If you use the values syntax you are going to supply the data 'by hand', usually from literals or variables.

It depends on the shape of the pear but you could try something along the lines of....

BEGIN

DECLARE varSaveCode INTEGER;

INSERT INTO A_Sales_Ledger(xxx, yyyy, Customer_ID)

SELECT xxx, yyy, Customer_ID FROM Customers WHERE Cust_Name LIKE '%Unique String%';

SET varSaveCode = SQLCode;

IF varSaveCode <> 0 THEN

     -- Some appropriate error handling, maybe....

     MESSAGE 'Oops - a Nashi happened, code '||varSaveCode TO CONSOLE;

     ROLLBACK;

ELSE

     COMMIT;

END IF;

END

-- NB, untested code - I think MESSAGE can cope with the mix of types

(You could try MESSAGE to CLIENT - it depends on the ability of your client software to trap the message.)

HTH, Paul

[Former Member · Accepted solution]

Paul

The code you supplied initially was bang on - I marked it as the answer.

It was an oddball situation that warranted a quick remedy (Sybase Central) rather than write a client interface

Thank you for taking the time to help 🙂