Problem with data in a text file

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with data in a text file

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2004-10-20 : 05:18:30

Hi,

I need to create a temporary table and load it with Customer ID's.
I have these ID's in a text file (.txt) - approx 80 000 ID's.

Is there some way to do it - like:
Select CustomerID from "file", where "file" is a .txt file?
I am using MS SQL Query Analyzer.

I need this to get data on customers from a permanent table on SQL Server, but only those customers I have in this text file.
I guess something like:

SELECT ... FROM
TABLE
WHERE CUSTOMERID in (80 000 ID's as hard input)

would not work well.

Thanks,
Katarina

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-20 : 05:26:58

Gee!

quote:
s there some way to do it - like:
Select CustomerID from "file", where "file" is a .txt file?
I am using MS SQL Query Analyzer.

I havn't encounter such problem like yours! You mean? you have to querry on a text file?

Want Philippines to become 1st World COuntry? Go for World War 3...

a_shyam41
Starting Member

9 Posts

Posted - 2004-10-20 : 05:33:58

Use Data transformation services in Enterprise manager and convert the data in text file to a temporary table (say temp1)

then use your sql statement as:

SELECT ... FROM
TABLE
WHERE CUSTOMERID in (SELECT CUSTOMERID FROM temp1)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-10-21 : 08:29:24

Check OpenRowSet and OpenDataSource in BOOKS ON LINE - I think you can use one of them to query a txt file.

You can't teach an old mouse new clicks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-21 : 08:32:12

In addition to DTS and OpenRowset/OpenDataSource, look at BULK INSERT and bcp too.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-21 : 12:24:41

As Richard noted, you can query from a text file;

SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\";Extended Properties="Text;HDR=No;FMT=FixedLength"')...stats#txt

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

katarina07
Starting Member

31 Posts

Posted - 2004-10-28 : 06:51:26

Thanks people.

I did as a_shyam41 suggested and it worked,

then I was on holiday, so I will see the other replies now.

Katarina

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources