CODEWITHSUNDEEP
snowflake-cloud-data-platformcasesql-insert
bostonbball21
bostonbball21

Reputation: 1

Using CASE WHEN to insert into a new table with values() clause - Snowflake SQL (classic web interface)

I'm trying to use insert into() and values() based on an existing condition in my original table to create a new table. I do have working code that uses insert into() along with a select and where clause but I'm trying to see if it's possible to do a CASE WHEN statement outside of the values so that certain values are inserted into the new table based on a conditional.

-- Creation and inserting values into table invoice_original
create temporary table invoice_original (id integer, price number(12,2),
                                         purpose varchar);
insert into invoice_original (id, price, purpose) values
  (1, 11.11, 'Business'),
  (2, 22.22, 'Personal'),
  (3, 33.33, 'Business'),
  (4, 44.44, 'Personal'),
  (5, 55.55, 'Business');
  
  
--  Creates final empty table invoice_final
create temporary table invoice_final (
  study_number varchar,
  price number(12, 2),
  price_type varchar
);

Code:

execute immediate $$
declare
  new_price number(12,2);
  new_purpose varchar;
  c1 cursor for select price, purpose from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        new_purpose := record.purpose;
        
-- This code runs!                
       insert into invoice_final(study_number, price, price_type)
       select 1, :new_price, 'Dollars'
       where :new_purpose ilike '%Business%';

       insert into invoice_final(study_number, price, price_type)
       select 2, :new_price, 'Dollars'
       where :new_purpose not like '%Business%';



-- Does not run but this is what I'm trying to do instead  
        CASE  
        WHEN :new_purpose ilike '%Business%' then 
        insert into invoice_final(study_number, price, price_type) 
        values('1', :new_price, 'Dollars')
        ELSE 
        insert into invoice_final(study_number, price, price_type) 
        values('2', :new_price, 'Dollars') END
        
  end for;
end;
$$;

This is just a simplified example of what I'm trying to do as whole, but just really wondering if a case when insert into() values() is possible in this scenario.

Upvotes: 0

Views: 1314

Answers (3)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

I will write some example code soon.

But the form

INSERT INTO (SELECT FROM VALUES WHERE)

Allows all your N value lines to be present. And the CASE logic to be in the WHERE and the result SUB-SELECT is INSERTed.

The ofther option if you are in Snowflake Scripting is to use an IF around those INSERTS verse using a CASE

What I was meaning, done with "full SQL variables as my example is not part of a loop" is you can move the SQL around like so, and have one block:

create temporary table invoice_final (
  study_number varchar,
  price number(12, 2),
  price_type varchar
);
 
set new_purpose = 'Business';
set new_price = 10.0::number(12, 2);
 
insert into invoice_final(study_number, price, price_type)
SELECT column1, column2, column3
FROM VALUES 
        ('1', $new_price, 'Dollars'),
        ('2', $new_price, 'Dollars')
WHERE CASE WHEN $new_purpose ilike '%Business%' then '1' ELSE '2' END = column1;

Thus for you loop, the three $ would be replaced with :, but this inserts just one row.

the IF was meaning are the Snowflake Scripting IF, but there is examples of using CASE just below that also.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

The CASE statement is allowed as branching construct:

Changes:

a) using INSERT INTO SELECT

b) each statement must end with ;

execute immediate $$
declare
  new_price number(12,2);
  new_purpose varchar;
  c1 cursor for select price, purpose from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        new_purpose := record.purpose;
           
        CASE  
        WHEN :new_purpose ILIKE'%Business%' THEN
          INSERT INTO invoice_final(study_number, price, price_type) 
          SELECT '1', :new_price, 'Dollars';
        ELSE 
          INSERT INTO invoice_final(study_number, price, price_type) 
          SELECT '2', :new_price, 'Dollars';
        END CASE;
        
  end for;
end;
$$;

Disclaimer: Using cursor loop and loop in general should be used when there is no way of rewriting the code to set-based approach.

INSERT INTO invoce_final(study_number, price, price_type)
SELECT CASE WHEN purpose ILIKE'%Business%' THEN 1 ELSE 2 END,
       price,
       'Dollars'
FROM invoice_orginal;

Upvotes: 1

Pankaj
Pankaj

Reputation: 2746

You need to use INSERT ALL. Refer here for more.

Change code as below

execute immediate $$
declare
  new_price number(12,2);
  new_purpose varchar;
  c1 cursor for select price, purpose from invoice_original;
begin
  for record in c1 do
        new_price := record.price;
        new_purpose := record.purpose;

INSERT ALL
when npurpose ilike '%Business%' then
 into invoice_final(study_number, price, price_type)
 values ('1',nprice,'Dollars')
else
 into invoice_final(study_number, price, price_type)
 values('2',nprice,'Dollars')
select :new_price as nprice, :new_purpose as npurpose;

  end for;
end;
$$;

Executing above will produce following result -

select * from INVOICE_FINAL;
STUDY_NUMBER PRICE PRICE_TYPE
1 11 Dollars
2 22 Dollars
1 33 Dollars
2 44 Dollars
1 56 Dollars

Upvotes: 1

Related Questions