This is for SQL Server 2005
I have a tab delimited file that once a day will be imported to a table. It will also need to be imported whenever someone pushes a button on the program thats viewing this data.
the DTS package or procedure also needs to set a last modified field on the table so that the user knows when the last time the data was imported.
Is a stored procedure the best way to import the tab file and have the ability to be called from a sql command? Does anyone have a link or any information that can get me started?
If you want to insert through Bulk Insert then check the below link for code
Rajesh Jonnalagadda http://www.ggktech.com
Here is example with BULK INSERT (you can call this script from a client application):
Given tab delimited text file with values (data.txt):
abc 10 z def 9 k
You can create a staging table for the imported data. Here the Data table is your production table, and Staging is only for importing.
CREATE TABLE Data ( keycol INT IDENTITY PRIMARY KEY, col1 VARCHAR(10), col2 INT, col3 VARCHAR(10), datecol DATETIME DEFAULT CURRENT_TIMESTAMP); CREATE TABLE Staging ( col1 VARCHAR(10), col2 INT, col3 VARCHAR(10));
Then you can run the following script to import data:
TRUNCATE TABLE Staging; BULK INSERT Staging FROM 'c:\data.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n'); INSERT INTO Data (col1, col2, col3) SELECT col1, col2, col3 FROM Staging; SELECT keycol, col1, col2, col3, datecol FROM Data; /* keycol col1 col2 col3 datecol ------- ----- ----- ----- ----------------------- 1 abc 10 z 2009-07-09 10:24:08.213 2 def 9 k 2009-07-09 10:24:08.213 */