Importing files using a stored procedure

[JCatRiggs]

I'm new to the SQL world and I'm not sure if what I want to do is possible.

I want to create a stored procedure that opens and reads a tab delimited file, determines if the row exsist on a table, and updates/inserts that row in the table.

I could do this using JAVA but would need to make a call to the server for each row. I was hoping to be able to do the processing at the server level. If anyone knows if this is possible please let me know how it can be done.

 

[RickCole]

You could use DTS package to import the file to a temporary table. Once you have the data in the temp table you can use SQL to compare and insert the data to the parent table as required.

Rick.

 

[JCatRiggs]

How do execute a DTS from a stored procedure?
I know how to create one using the wizard but not how to run it a stored procedure.

 

[Karridy]

Check out SQL's BCP application. BCP allows you to import a text file into SQL, and it is faster than DTS! What's more, is that you can use SQL's xp_CmdShell stored procedure to execute this for you. If you have SQL Installed on the machine that you are using, goto a command prompt, and type "BCP". Hit return to get a listing of options. If the options aren't clear, here is an example. I actually did this yesterday!
Replace all of the info that is in <>'s
-----------------------------
CREATE PROCEDURE <ProcedureName>

AS
declare @bcp1 varchar(255)
select @bcp1 = 'bcp <Database.owner.Table> in &quot;<PathToFile ex: \\WebServer\Inetpub\

http://wwwroot\Client\Uploads\Datafile.txt>&quot;

/c /r<EndOfRowDelimeter> /t<ColumnDelimeter> /U<Login> /p<password> /S<ServerName>'
execute master..xp_cmdshell @bcp1
GO
-----------------------------

Let me know if you have any probs.
**If you are executing this StoredProcedure from ASP/ADO, make sure that you are Connecting a a SQL Login that has system administrator permissions. That is the only user that is able to execute the xp_CmdShell Stored Procedure.

Fore more info, goto:?

http://www.windowsitlibrary.com/Content/77/14/1.html#1

Have Fun,
Karridy@Karridy.com

 

[fluteplr]

Or if you want to use DTS, there is a way but it is kind of wierd.

Right click on your DTS package in Enterprise Manager and schedule it does not matter for when.

After the job is created, go into the job and delete the schedule.

You can then run the job with the following procedure.

USE msdb
EXEC sp_start_job @job_name = 'My Job Name'

One drawback is that the user running this stored proc either needs to be a System Admin or own the job.

 

[Dragonsoft]

Dear JCatRiggs.
I would use the Bulk Insert function in TSql.
this will read a text file with any delimiter you choose and insert that data into a database table.
Here is an example of such a procedure, just paste it in Query Analyzer change the path in the Bulk insert function to suit your needs and press F5 to compile.
Save the example file-data(below the proc) to a file and let the procedure loadit into the temp-table for you.
To execute the proc write: 'exec bult_proc' .
Hmm DTS packages are powerful but a bit akward if you are not used to them so hope this will help.
Dragonsoft. torgbosse@hotmail.com

-->Example procedure -->
use pubs -- if you have it, or use another db.

If Exists ( Select [name] From sysobjects Where [name] = 'bulk_proc' And type = 'P' )
Drop Proc bulk_proc
Go
Create Proc bulk_proc
As

-- Create temp table, will be dropped automaticly when proc finnishes. --
Create Table #tmpValues(
rownum INT,
[name] VARCHAR(30),
sname VARCHAR(30)
)

-- Insert values into temptable with Bulk Insert from a textfile with comma delimiter.
-- other delimiters can be used. (se bulk insert in the &quot;Books online&quot.

Bulk Insert #tmpvalues
From 'G:\names.txt'
With(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '|\n'
)

-- Just show the temptable, this table can be used for working with the data. --
Select
rownum,
[name] + ' ' + sname As &quot;Name&quot;
from #tmpvalues
/*
exec bulk_proc -- Select this line to execute proc.
*/

--> Example textdata to save to a file:
200,Liza,Minnelli|
201,Boris,Karloff|
202,Spice,Melange|

 

[JCatRiggs]

Thanks everyone I'm going to look at all the suggestions and try and figure out which works best for me

JCatRiggs