inserting records from specific columns in one table to another table depending on multiple criteria
I’m Using SQL server and need to Inset records from one table into another empty table. the first table im pulling from is called accnt and has the fields: code, invno, invdate, ven, and amnt, and imported. the table that the records are going to is called quick and has the fields: date, num, name, account, split, and amount. some of the fields are just directly copied which i understand, but what im having trouble with is that some of the fields need to be populated with a different field from accnt depending on the value in account. and there is a field in accnt called imported that is either empty or has an x in it. i only want to import records where the imported field is empty
the insert statement i have so far should import: date, name, account, and amount directly not depending on the anything. But the num field imports from a different field in accnt depending on the value of the accno field. the criteria is:
if account < 7000 then num = code
if account > 7000 then num = invno
here is what i tried, but it didnt work
DELETE FROM quick INSERT INTO quick (date, num, name, account, amount) if accnt.accno < '7000' then SELECT invdate, code, ven, accno, amnt from accnt if accnt.accno > '7000' then SELECT invdate, invno, ven, accno, amnt from accnt
how do i accomplish what i’m trying to do? the table looks like this even though the data in the picture is wrong:
the type and split fields will be populated later with an update query.
One Solution collect form web for “inserting records from specific columns in one table to another table depending on multiple criteria”
Try it with a single SELECT and use CASE to separate the two situations.
DELETE FROM quick INSERT INTO quick (date, num, name, account, amount) SELECT invdate, case when accnt.accno < '7000' then code else invno end , ven, accno, amnt from accnt