November 2, 2009 at 5:14 pm
Here is the query i have now.i need to change it so i can lookup multiple items.Right now it grabs the value assigned to @DCN.and loops thru until it runs into a Null value.I would like to be able to put in a list of values and have it run the one at a time until its done.
Any help would be appreciated.
Declare @DCN char(12)
set @DCN = '091026566001'
DECLARE @History TABLE
(
[DCN] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqNum] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessDate] [datetime] NOT NULL ,
[ReceiveDate] [datetime] NOT NULL ,
[Source] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Stack] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ScanDate] [datetime] NULL ,
[Application] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileGroup] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClaimType] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Batch] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Transmission] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [datetime] NULL ,
[RejectCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectDate] [datetime] NULL ,
[RejectAction] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeqIn] [datetime] NULL ,
[WeqOut] [datetime] NULL ,
[StatusId] [int] NOT NULL ,
[BoxNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldDCN] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCNCount] [int] NOT NULL ,
[ImagesCount] [int] NOT NULL ,
[XrayImagesCount] [int] NOT NULL ,
[Region] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceCenter] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POBox] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttInd] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Category] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NPISort] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SpecialOps] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DomesticKey] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DomesticCustomer] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DtlLines] [int] NOT NULL ,
[Charges] [int] NOT NULL ,
[SourceSource] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceStack] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceSeqNum] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TotalMinsToTransmit] [int] NULL,
[MidProc] [int] NULL
)
SET NOCOUNT ON
DECLARE @OldSource CHAR(2)
WHILE (@DCN IS NOT NULL) BEGIN
INSERT INTO @History SELECT * FROM dcnmaster (NOLOCK) WHERE DCN = @DCN
IF @@ROWCOUNT = 0 BEGIN
RAISERROR('INVALID DCNMASTER RECORD(S)', 16, 2)
SET NOCOUNT OFF
END
SELECT @DCN = OldDCN, @OldSource = Source FROM dcnmaster (NOLOCK) WHERE DCN = @DCN
IF @OldSource = 'FT' GOTO NOMORERECS
END
NOMORERECS:
SELECT * FROM @History
SET NOCOUNT OFF
November 2, 2009 at 11:08 pm
why can't you just insert the data in the history table? i.e.
Insert into History (all your fields go here) select column1,col2,.... from OldHistory where @DCS is not null
that should work.
November 3, 2009 at 5:16 pm
That wont work because each dcn that it looks up will come to a null value.after it hits that null value i need to to grab the next dcn in the list.
November 5, 2009 at 10:40 am
Give up and use SSIS, it's much easier.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply