inserting records from specific columns in one table to another table depending on multiple criteria

debugcn Published at Dev

Vbasic4now

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:

enter image description here

the type and split fields will be populated later with an update query.

Greg Viers

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-9

Comments

0 comments

From Dev

Related Related

Article

inserting records from specific columns in one table to another table depending on multiple criteria

inserting records from specific columns in one table to another table depending on multiple criteria

Display records from one table which is not in another table by multiple columns

Mysql Query for inserting from one table to another on Multiple conditions and columns

Mysql Query for inserting from one table to another on Multiple conditions and columns

Pulling records from one table (products) depending on a value in another (department)

inserting table values from one table to another

Inserting values of one column of a table into different columns of another table

Inserting from one database table to another

Inserting from one database table to another

How do I insert records from one XML file into another at specific points based on multiple criteria?

Insert specific rows from one table in database into another with different columns

Select / Update records in one table that are LIKE words selected from another table column with multiple records

Laravel inserting multiple records in table

Copying multiple columns from one SQL table to another

Select values from one table depending on referenced value in another table

Auto-populate one table from another table if a criteria is met

Inserting data to another table based on some criteria

Randomly Assign Records From One Table to Another

Select from one table if no records found in another

Extract one column from sql table and insert into another table as multiple records coupled with different values

How to insert multiple records from one table into another on update of third table in MySQL

How to get all records from one table and only records from joined table with criteria

Selecting one record from two tables and multiple records from another table in ONE query

Read (lookup multiple criteria) records from database table, and Write to database table with matching criteria

Select records from a table where two columns are not present in another table

One record from multiple records in the table

Vlookup on another table with multiple criteria

Copy specific columns from one table to another table, and include the source tablename

How to insert specific columns and rows of data to an existing table from one table to another?

Build columns of Yes / No depending on data from another table in mysql