All Forums Tools
thisisKumar 13 posts Joined 09/11
24 Apr 2013
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

dnoeth 4628 posts Joined 11/04
24 Apr 2013

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

Dieter

thisisKumar 13 posts Joined 09/11
24 Apr 2013

 

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)

 

dnoeth 4628 posts Joined 11/04
24 Apr 2013

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

Dieter

thisisKumar 13 posts Joined 09/11
24 Apr 2013

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.

You must sign in to leave a comment.