Text Files and Stored Procedures

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

Import/Export (DTS) and Replication (2000)

Text Files and Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-04 : 11:32:02

Ben Gulley writes "I need to update a table in my DB using information from a text file. I know I can use DTS to import the data to a table in the db and then run an update n the table but I do not want the overhead of the extra table in my DB. I thought I might use a stored procedure to import the data into a #temp table then run the update there but I can not figure out how to work with text files within a SP. Temp tables are not available to DTS so that is out as well? I guess my question here is: "What is the best way to update a table using a text file, on a regular basis, without the overhead of a separate table?""

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 12:11:33

Don't think there is any way to import a file without a new connection on which the temp table will be unavailable.
You can use a global temp table ##tbl which will be available to all connections and automatically droped when the last connection closes.
This shold work fine with dts and bulk insert.

(Good to see that you didn't consider using dts to do the update too).

I would do this with a permanent table which gets truncated before and after the insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rharmon
Starting Member

41 Posts

Posted - 2002-09-04 : 12:54:47

I find myself doing a LOT of work with text files. Because of this, I've added a "text linked server" on one of my servers.

From BOL:
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO

--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

from there, I just dump the text files in the likned server directory and query against them just like they're tables on a linked sql server. Keeps the stored proceedures short and keeps me away from DTS.

Hope this helps,

ROb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources