Avoiding duplicates in the stored procedures in snowflake

user_13138455 0
Viewed 464

I have written a stored procedure to insert values into the table. But when I called the stored procedure twice, the duplicate values are being inserted into the table. How to avoid duplication of values in stored procedure.

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE get_columns(TABLE_NAME VARCHAR)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
var stmt = snowflake.createStatement({
    sqlText: "SELECT * FROM " + TABLE_NAME + " LIMIT 1;",    
});
stmt.execute();

var cols=[];
for (i = 1; i <= stmt.getColumnCount(); i++) {
  INSERT INTO TABLE1 (Col_Name) VALUES ('+"stmt.getColumnName(i)"+');  //DUPLICATE VALUES
}
return cols
$$;

Edit:

I'm retrieving the column names of a Col_table say (Col1, Col2, Col3) and I want these column names into TABLE1.

I called the procedure call get_columns(Col_table) and the column names are inserted into TABLE1.

When I again call the procedure call get_columns(Col_table), TABLE1 will have duplicate values as (Col1, Col2, Col3, Col1, Col2, Col3). I want to avoid duplication either by using merge or update or if any other method.

snowflake-cloud-data-platform stored-procedures sql javascript
1 Answers

Based on the edited question and comments:

  1. "When I again call the procedure [...], TABLE1 will have duplicate values [...]. I want to avoid duplication either by using merge or update or if any other method"
  1. "The INSERT OVERWRITE is replacing the rows of the table when I called the procedure on two different table columns"

Then, if you don't want to insert repeated values into an existing table, you can do something like this:

INSERT INTO TABLE1(Col_Name) 
SELECT *
FROM (SELECT 'i' x)
WHERE x NOT IN (SELECT Col_Name FROM TABLE1);

Replace that SQL code inside your procedure, and you won't see repeated values. Replace 'i' for the value you want to insert.

(as a general comment: I'm not sure the procedure in the question is the most effective solution to the overall stated problem, but this will fix the specific problem of having duplicate rows)

user_1324380
Related