Re: Do Actions based on Content of Fiel with a Job
From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 06/23/04
- Next message: CC&JM: "Choose database dinamically"
- Previous message: Narayana Vyas Kondreddi: "Re: where to get more info on..."
- In reply to: DraguVaso: "Do Actions based on Content of Fiel with a Job"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Jun 2004 07:55:37 -0700
I think your post has a solution that would work. Meaning inserting the
records into a holding table, then programmatically taking records from that
table and inserting them into the appropriate table. Now how would you do
it. Clearly it appears that each record type has a little different format.
So I might consider doing something like this. Hope this rambling makes
sense.
1) Load the records into a holding table. You can use do this a number of
different ways, BCP, DTS, xp_cmdshell, etc.
2) The process each type code inserting and updating the appropriate tables
using different insert and update statements like so some thing like this:
Insert into tblDossiers(D01,D02,...) select
substring(rec,4,10),substring(rec,14,4),....from holdingtable where
substring(rec,1,3) = 'DI '
Insert into tblTelephones(D01,....) select substring(rec,4,10),....from
holding table where substring(rec,1,3) = 'TI '
update tblDossiers
set D01 = substring(rec,5,10),
set D02 = substring(rec,14,4),.....
from HoldingTable
where substring(rec,1,3) = 'DM1' and ....
-- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---- Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples "DraguVaso" <pietercoucke@hotmail.com> wrote in message news:OX96b0PWEHA.1048@tk2msftngp13.phx.gbl... > Hi, > > I receive Files with records like this: > " > DI 41657119149001101VTG CL1 NMR CORVERS BRUNO > MMEMATHIJS VERONIQUE C > TI 011745470 4165711914 > DM141200348990100 > " > I have to do different actions with the records based on the first three > characters of each record. > > For exemple: > DI 41657119149001101VTG CL1 NMR CORVERS BRUNO > MMEMATHIJS VERONIQUE C > -> DI : INSERT into tblDossiers: "4165711914" in Field DO1, "9001" in DO2, > "NMR CORVERS BRUNO" in Field Client1 etc... > > TI 011745470 4165711914 > -> TI : INSERT into tblTelephones: "4165711914" in Field DO1, "011745470 " > in Field Tel etc > > DM141200348990100 > -> DM1: UPDATE in tblDossiers: ... > > > My quiestion is: how shoudl I do this the best way? Import everything in one > Table and than let a big Stored Procedure determine the actiosn based on the > first three characters? How woudl this Stored Procedure look like? Or is > there some standard option in the Job Sheduler that I can use for thisk idn > of stuff? If yes: how? > > Thanks a lot in advance!! > > Pieter > >
- Next message: CC&JM: "Choose database dinamically"
- Previous message: Narayana Vyas Kondreddi: "Re: where to get more info on..."
- In reply to: DraguVaso: "Do Actions based on Content of Fiel with a Job"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
