Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-17
10
Medium Priority
?
607 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:yguyon28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 332 total points
ID: 34158717
If you are on SQL Server 2008, you should set up an SSIS package that has a control table that contains the most recent update of the database table.  Then, if the file is newer than the table's last update, truncate the table and import the text file's data into it.
0
 

Author Comment

by:yguyon28
ID: 34158990
How do I set up an SSIS package.  I need some simple instruction...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34159271
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).
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:yguyon28
ID: 34159316
No I have never done any SSIS package.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34159515
in the sql server 2005 or above setup media, you can CHECK the INTEGRATION SERVICE on setup wizard.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34159654
@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.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 332 total points
ID: 34159775
@8080_Diver,
I am agree with you, but this thread can be a starting point for vguyon28 and first step is to install SSIS.

@vguyon28,
Note that you can do it without SSIS too, with import whole file in a staging table with sql scripts , then using MERGE command for UPSERT purpose or any other commands to do proper operations, but SSIS will make it simpler, and this is your choice to select the method.
If you mind to go with SSIS, install SSIS, and go through walkthroughs and spend time on it
If you want to go without SSIS, tell us what exactly you want to do? do you want to load all data from text file into table? or you want Update/Insert procedure? or anything else
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34160165
@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?
0
 

Author Comment

by:yguyon28
ID: 34197776
8080_Diver

No I don't have SSIS installed.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question