insert from file based on criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert from file based on criteria

Author

Topic

mukeshguliaO
Starting Member

2 Posts

Posted - 2011-03-18 : 02:51:50

Hi all,

I have a .dat file in the following format

EXTID|ICI_ID
88|2530016690
2047|2530084990
92|2530036590
95|ENORBKNRX
16376|2530138490
5344|2530099590
5345|2530095490
7|2530010590
Refco Cap Mkts CUST|2530487590

And I have two tables

table1 --(colname1, colname2, colname2, colname4)
table2 --(.., colname5, colname6, colname7...)

I need to insert the data of .dat file into table1
Insert EXTID into colname1
Insert corresponding ICI_ID into corresponding colname2
Insert "STATIC" into corresponding colname3
Insert sysdate into corresponding colname4

Now the criteria is
Insertion will only happen when
1. EXTID is not currently present in table1
AND
2. there is an entry in table for
((ICI_ID=table2.colname5) OR (ICI_ID=table2.colname6)) AND colname7 IN('STRING1', 'STRING2')

I wrote a program in java to do this task for me. But as the file is very large consisting of nearly 1 lakh record, I thought there must be some way of doing this in SQL alone-- by some query or small procedure..
I would be glad if anyone can help me on this..

thanks.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-18 : 03:31:20

Use bulk insert to load all the data in a temp table and then insert it into main table according to your criteria.

Vaibhav T

If I cant go back, I want to go fast...

mukeshguliaO
Starting Member

2 Posts

Posted - 2011-03-18 : 05:06:11

OK. I will try that..
between I was trying to Bulk insert
[CODE]BULK INSERT TEMP_OTC_CSA_ID FROM 'c:\OTC_n.dat' WITH (FIELDTERMINATOR = '|')[/CODE]

this give an ora-00900: invalid sql statement error

is there a problem with this command. pls help. i am not good in sql.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-18 : 05:19:23

Try this -


BULK
INSERT TEMP_OTC_CSA_ID
FROM 'c:\OTC_n.dat'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM TEMP_OTC_CSA_ID

Vaibhav T

If I cant go back, I want to go fast...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-03-18 : 09:36:08

try asking your question on an oracle forum. this one is for MS SQL.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources