Updating data in a table for multiple records from a file using Import

Tools
Enthusiast

Updating data in a table for multiple records from a file using Import

hi , Currently by using BTEQ and Teradata SQL assitant I am able to insert records into a table.

But I want to update records in a table. I have more than 2000 records in a table and i have 200 records in a file

both contain a same Primary key column and if there is no match found i want to insert a new row in a table.

so, its a UPDATE else INSERT scenario for mutliple records from a file to database table.

Thanks,

Kumar

4 REPLIES
Senior Apprentice

Re: Updating data in a table for multiple records from a file using Import

Hi Kumar,

the syntax you're looking for is a MERGE INTO, please check the manuals.

There's also an old UPDATE ELSE INSERT syntax, but MERGE is more flexible.

Dieter

Enthusiast

Re: Updating data in a table for multiple records from a file using Import

Thanks, Dieter.

MERGE INTO function is reqally useful for me in this case when I have only couple records.

But, can we use this MERGE INTO along with INSERT INTO?

I wanted to update else insert a table from a file.

CREATE TABLE KUMAR_TEST

(cust_ky INTEGER, fname VARCHAR(30), lname VARCHAR(50))

PRIMARY INDEX(cust_ky);

INSERT INTO KUMAR_TEST VALUES(1,'abc','def');

MERGE INTO KUMAR_TEST USING

VALUES(1,'pqr','xyz')

AS kk(a,b,c)

ON cust_ky=kk.a

WHEN MATCHED THEN 

UPDATE

SET fname=kk.b

,lname=kk.c

WHEN NOT MATCHED THEN

INSERT VALUES(kk.a,kk.b,kk.c)

--------


MERGE INTO KUMAR_TEST USING

VALUES(?,?,?) --how to take values from a file

AS kk(a,b,c)

ON cust_ky=kk.a

WHEN MATCHED THEN 

UPDATE

SET fname=kk.b

,lname=kk.c

WHEN NOT MATCHED THEN

INSERT VALUES(kk.a,kk.b,kk.c)

Senior Apprentice

Re: Updating data in a table for multiple records from a file using Import

Hi Kumar,

based on your description i thought you already know how to import using SQLA/BTEQ.

Assuming your data is in a readable delimited format:

In SQLA set the delimiter to the correct char (in tools - options - export/import) and use question marks as you showed in your query.

And in BTEQ

.import vartext 'your delimited char' file = yourfile.txt;

USING a (varchar(xx))), b (varchar(xx)), c (varchar(xx))

MERGE INTO KUMAR_TEST USING
VALUES(:a,:b,:c) 

...

Dieter

Enthusiast

Re: Updating data in a table for multiple records from a file using Import

Thanks, Dieter. I think I got the solution.

for this I have created the temporary table and imported data from a file 

then by using this temporary table I am updating the actual table using MERGE INTO

Step 1: CREATE GLOBAL TEMPORARY TABLE abc  -- This is exactly same as original table

 (cust_ky INTEGER, fname VARCHAR(30), lname VARCHAR(50)) 

ON COMMIT PRESERVE ROWS;

Step 2: In SQLA set the delimiter to the correct char (in tools - options - export/import)

Step 3: then imported data from a file. (created few sample records in a file)

INSERT INTO abc (cust_ky, fname,lname) VALUES (?,?,?)

Step 4: Updated my actual table using MERGE INTO

MERGE INTO myDEVschema.kumar_TEST USING (SEL cust_ky, fname, lname FROM abc)AS kk(a,b,c)

ON cust_ky=kk.a

WHEN MATCHED THEN 

UPDATE

SET fname=kk.b

,lname=kk.c

WHEN NOT MATCHED THEN

INSERT VALUES(kk.a,kk.b,kk.c)

this is all using SQLA only.