Reading from an existing text file (.txt) in SQL Server 2005

Question

My gps device writes its data to a text file on my server. Now I want the sql server to read it and save the values ​​in a table. How to start this scenario? Any suggestion.

EDIT: Think this is an existing file with 10 lines, and I already imported it. Now the file is updated with new 10 lines. How to import new rows into sql server?

Answer 1

Here is an example solution:

/** Table to hold GPS data **/ CREATE TABLE Pings ( RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, PingDate DATETIME, Lat FLOAT, Long FLOAT ) /** View for bulk insert **/ CREATE VIEW V_Pings AS SELECT PingDate, Lat, Long FROM Pings 

GPS data comes from a channel delimited file> C:\GPS\Pings

 2010/01/01 00:00:00|30.1|50.1 2010/01/01 00:00:01|30.1|50.2 2010/01/01 00:00:02|30.1|50.3 2010/01/01 00:00:03|30.1|50.4 2010/01/01 00:00:04|30.1|50.5 

You have a stored procedure that interrupts through SQL Agent:

 CREATE PROCEDURE usp_LoadPings AS DECLARE @firstRow INT, @sql NVARCHAR(1000) SELECT TOP 1 @firstRow = RowID + 1 FROM Pings ORDER BY RowID DESC SET @sql = N' BULK INSERT V_Pings FROM ''C:\GPS\Pings.txt'' WITH ( FIELDTERMINATOR =''|'', ROWTERMINATOR =''\n'', FIRSTROW = ' + CAST(@firstRow AS NVARCHAR(50)) + ' )' EXEC(@sql) 

A stored procedure does not load data if there is no new rowset, starting with the last row loaded into the table.

I understand that this is just like implementing a devmake response, but I really created it separately. However, I supported his answer, since he published his first.

Answer 2

You can use BULK INSERT . Keep the count of the rows you inserted and set the next FIRSTROW parameter to BULK INSERT the next time to start working with new rows.

Similarly, you can use the bcp utility and set the -f option.

Answer 3

SqlBulkCopy , or TSQL BULK INSERT , or, if data conversion is not required, SSIS Bulk Insert Task .

See:

• About mass import and mass export operations

• SSIS: using bulk upload task with csv files

• Tutorial: Creating a Simple ETL Package

Answer 4

Make sure the file path is correct. This may be relative if it lives on the same machine as the sql server, but if you don't need an absolute path. In addition, the sql server will require access rights to this file. Maybe not here, but that's fine.