Update a Table from a text file by running a store procedure......
Posted on 2010-11-17
I have a table: TableTest in Mircosoft SQL Server that I would like to get updated on a regular basis when the following text file get updated. All the content of the text file need to replace the content of the table. This need to be an automatic process. The text file does get updated by itself but I need to create a process that take the content of the text file and replace all the content of the database.
Should I write a store procedure for this and if so? How do I do this?
And if so I need some clear example on how to do this. I’m new to this. I have posted the same questions in the past but never really.
Also I have created a store procedure bellow. Not sure if it’s correct.
Text File: Update.Txt
V04510;5/28/2009;00729783;Q10070849I;9/29-30/07 RMB INV#22738;156;SHORT, JUDY
/****** Object: StoredProcedure [dbo].[UpdateAp] Script Date: 11/04/2010 10:37:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Yves Guyon
-- Create date:
ALTER PROCEDURE [dbo].[UpdateAp]
-- Add the parameters for the stored procedure here
DECLARE @sql varchar(4000)
SET NOCOUNT ON
SET @sql = 'BULK INSERT TableTest FROM "C:\ap_test.txt" WITH (FIELDTERMINATOR = ";", ROWTERMINATOR="\n")'