Link to home
Start Free TrialLog in
Avatar of Stacie
StacieFlag for United States of America

asked on

Update a Table from a text file by running a store procedure......

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

Table TableTest
Table Field:
 [Vendor Number]
      ,[Date]
      ,[Check Number]
      ,[Reference Number]
      ,[Description]
      ,[Payment Amount]
      ,[payee]
  FROM [AdvancePayroll].[dbo].[TableTest]



USE [AdvancePayroll]
GO
/****** Object:  StoredProcedure [dbo].[UpdateAp]    Script Date: 11/04/2010 10:37:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Yves Guyon
-- Create date:
-- Description:    
-- =============================================
ALTER PROCEDURE [dbo].[UpdateAp]
    -- Add the parameters for the stored procedure here
   
AS

DECLARE @sql varchar(4000)

BEGIN
     SET NOCOUNT ON
 
    SET @sql = 'BULK INSERT TableTest FROM "C:\ap_test.txt" WITH (FIELDTERMINATOR = ";", ROWTERMINATOR="\n")'

END
Exec(@sql)
GO
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stacie

ASKER

How do I set up an SSIS package.  I need some simple instruction...
Start by opening VS 2008.  Then start a new project but select a database project.  You should then be able to select an SSIS project.

Now, I have to ask, have you ever done a DTS package for SS2000?  It is somewhat different when you start fworking with SSIS but the concepts are very similar.  However, if you haven't ever done a DTS package, much less an SSIS package (which you have apparently not done ;-), then you may be in for some excitement.

There are some great books on the subject (e.g. Knight's 24-hour Trainer SQL Server 2008 Integration Services might be a good place to start).
Avatar of Stacie

ASKER

No I have never done any SSIS package.
Avatar of Reza Rad
in the sql server 2005 or above setup media, you can CHECK the INTEGRATION SERVICE on setup wizard.
@reza_rad,

Somehow, I don't think getting the Integration Service installed is going to be vguyon28's biggest worry.

@vguyon28,
However, you may be able to use the Import Data Wizard to set up the import process and then save that as an SSIS package.  That package will not have any error handling and, if you ever look at it, it will be a really ugly bit of SSIS; however, it will work if the data is set up correctly in the flat file.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@reza_rad,

No need for trying to do an UpSert, the OP indicates that the whole table is to be replaced with each upload.

@vguyon28,
It is correct that you should be able to handle it all with SQL scripts.  However, as I noted above, if there is a problem due to any sort of invalid data, you could be in a bit of trouble.  Those things are easier to handle in SSIS. ;-)

By the way, do you have SSIS installed?
Avatar of Stacie

ASKER

8080_Diver

No I don't have SSIS installed.