November 24, 2009 at 1:54 am
Hi,
I have two tables, on the basis of rules defined in one table i have to insert values into other table.
source table : sc
rules table : cri
1. Records having same values of id1 and flag =1 , need to be merged in a new record.
2. Refer to table - Cri, take the value of each field from the record based on highest priority of source as defined in cri table. i.e. priority column tells from which record we have to take the value
3. the field has to be read from a particular source and the field vlaues are null for that source then go for the next preferred source
4. Consider the logical column, all the fields having the same value for logical should be read from the same record. (here line1 and line2 are logical so both the values must be taken from same record either one should be not null)
5. The resultant merged record should have flag1=2 and id = id1 of the records getting merged and inserted into the same table
CREATE TABLE [dbo].[SC](
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_SC] DEFAULT (newid()),
[Line1] [nvarchar](100) NULL,
[Line2] [nvarchar](100) NULL,
[Occ] [nvarchar](100) NULL,
[Source] [nvarchar](50) NULL,
[ID1] [uniqueidentifier] NULL,
[Flag1] [int] NULL,
CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED
(
[ID] ASC
))
INSERT INTO SC VALUES(NEWID(), 'RASSE','CDMRUST','GDR','CC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
INSERT INTO SC VALUES(NEWID(), 'RASSE','ST','GPR','RC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
INSERT INTO SC VALUES(NEWID(), 'RAK','RUST','GSMITH','CC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
INSERT INTO SC VALUES(NEWID(), 'RASSE','CDMRUST','GDR','CC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
INSERT INTO SC VALUES(NEWID(), 'RAKLINE','CDRLINE2','PR','RC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
INSERT INTO SC VALUES(NEWID(), 'RAKL1','CDR2','PR','RC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
CREATE TABLE [dbo].[Cri](
[Name] [nvarchar](50) NOT NULL,
[Source] [nvarchar](50) NOT NULL,
[Priority] [int] NULL,
[Logical] [int] NULL,
[TraceField] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Name] ASC,
[Source] ASC
))
INSERT INTO CRI VALUES('LINE1', 'CC',1,1,0)
INSERT INTO CRI VALUES('LINE2', 'RC',1,1,0)
INSERT INTO CRI VALUES('OCC', 'CC',1,0,0)
November 24, 2009 at 8:08 am
A couple of questions:
First, I can't even be sure from your text whether or not the "merge" (update?) is using the SC or the CRI table as it's target. Could you please show an example of what the expected output would look like after the job runs?
Second, how have you approached the problem? Sometimes it helps to be able to see the code you have already developed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2009 at 8:19 pm
thanks for ur reply.
a new record should be inserted into table SC - criteria -
multiple records having same values of id1 and flag =1 , need to be merged in a new record.
this new record should consist of updated values of occ, line1 and line2(which is logical)
for line1 if u look at Cri table the source is CC so the column should be updated from the record which holds CC as its Source.
as line2 and line1 is difined as logical the value should be taken from the record from where you have taken line1 value. if both are null then it should see the Cri table where the line1 has any second option... if it has then that source should be taken into consideration. and line2 also taken from that record.
for OCC there is no logical dependency. so the value of OCC should be taken from the source 'CC' if it is null then it should see the next value in cri table for OCC if any other source is mentioned then it should take from that source.
in the given code
( 'RASSE','CDMRUST','GDR','CC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
( 'RASSE','ST','GPR','RC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
( 'RAK','RUST','GSMITH','CC','AD6CBB1B-D737-441F-B74D-364FF42F8597',1)
result should be
('AD6CBB1B-D737-441F-B74D-364FF42F8597','RASSE','CDMRUST','GDR','CC','AD6CBB1B-D737-441F-B74D-364FF42F8597',2)
(NEWID(), 'RASSE','CDMRUST','GDR','CC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
(NEWID(), 'RAKLINE','CDRLINE2','PR','RC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
(NEWID(), 'RAKL1','CDR2','PR','RC','0BDD847E-C59A-4053-B2B7-39C039FFA290',1)
result should be
('0BDD847E-C59A-4053-B2B7-39C039FFA290', 'RASSE','CDMRUST','GDR','CC','0BDD847E-C59A-4053-B2B7-39C039FFA290',2)
additional data
INSERT INTO CRI VALUES('LINE1', 'RC',2,1,0)
INSERT INTO CRI VALUES('OCC', 'RC',2,0,0)
i have given only few column values. originally these tables consist of more than 100 columns to be merged. like line1, line2 there are many columns street, value etc....
so
1. i tried to achieve this using cursors and dynamic query first i've taken information from sys.columns and Cri table to find out the columns should be merged.
2. i've taken name,source, priority, logical of a particular name say 'line1' into a cursor
3. i've taken only the id1's which i should update into a staging table
4. then selected the value of line1 from the source into a variable. if that variable is null then it looks for next source system.
is there a way to achieve this without using cursors?
thanks for your patience,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply