August 8, 2009 at 4:59 pm
Dear Friends
I have three table, event,measure, and eventmeasure as follows:
CREATE TABLE [dbo].[event](
[eventId] [int] IDENTITY(1,1) NOT NULL,
[eventName] [varchar](50) NOT NULL
)
insert into event values('work order')
insert into event values ('downtime')
insert into event values ('work order')
insert into event values ('uptime')
insert into event values ('work order')
CREATE TABLE [dbo].[measure](
[measureId] [int] IDENTITY(1,1) NOT NULL,
[measureName] [varchar](100) NOT NULL
)
insert into measure values ('opt5')
insert into measure values ('opt4')
insert into measure values ('opt6')
insert into measure values ('opt7')
insert into measure values ('opt5')
insert into measure values ('opt5')
CREATE TABLE [dbo].[eventMeasure](
[eventMeasureId] [int] IDENTITY(1,1) NOT NULL,
[eventId] [int] NOT NULL,
[measureId] [int] NOT NULL
)
If i run this
select * from event where eventname='Work order'
I get :
eventid eventname
1work order
3work order
5work order
If i run this:
select * from measure where measurename='opt5'
I get:
measureid measurename
1opt5
5opt5
6opt5
I want to insert values into eventmeasure table as following:using the eventid from the 1st query and measureid from the second and inserting in eventmeasure as:
eventmeasureid eventid measureid
545611
545735
545856
How can i achieve this? Will i need a cursor for this?
Many thanks.
August 9, 2009 at 6:05 am
How do you connect eventide to MeasuerID? Is it only by inserting order? Also how did you get the values for EventMeasureID?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 9, 2009 at 6:06 am
This is the cursor that i used:
Please note: this is based on real data, data in the above post is just an example of how i would like the result:
declare @eventid int
declare evnt CURSOR FOR
select e2.eventid from event e2
where e2.eventid not in
(select
em.eventid
from tempeventmeasure em inner join event e
on em.eventid=e.eventid
inner join tempmeasure m
on em.measureid=m.measureid
where e.eventname='work order'
and m.measurename='engWoOpt5')
and e2.eventname='work order'
Open evnt
FETCH NEXT FROM evnt into @eventid
While @@FETCH_STATUS=0
BEGIN
DECLARE meas CURSOR FOR
select top 3 measureid from tempmeasure
order by measureid desc
declare @measureid int
open meas
FETCH NEXT FROM meas into @measureid
WHILE @@FETCH_STATUS=0
Begin
--Insert into tempeventmeasure
select distinct @eventid,@measureid from tempeventmeasure
where measureid not in (select measureid from tempmeasure where measureid=@measureid )
FETCH NEXT FROM meas into @measureid
end
close meas
DEALLOCATE meas
FETCH NEXT FROM evnt into @eventid
END
Close evnt
DEALLOCATE evnt
This is giveing me a result as:
906 1910
906 1909
906 1908
970 1910
970 1909
970 1908
908 1910
908 1909
908 1908
I want one to one record;
906 1910
970 1909
908 1908
Basically one measureid can belong to only one eventid. What am i doing wrong?
August 9, 2009 at 6:14 am
How do you connect eventide to MeasuerID? Is it only by inserting order? Also how did you get the values for EventMeasureID?
Eventmeasure table gets eventid from event table and measureid from measure table.
Eventmeasureid is the Primarey Key
Only one measure can belong to one event.
August 9, 2009 at 10:54 am
sarvesh singh (8/9/2009)
How do you connect eventide to MeasuerID? Is it only by inserting order? Also how did you get the values for EventMeasureID?
Eventmeasure table gets eventid from event table and measureid from measure table.
Eventmeasureid is the Primarey Key
Only one measure can belong to one event.
That's not what he's asking. There are six possible unique pairings of two sets of three numbers. How do you know which of those six is the correct one?
906-1910 970-1909 908-1908
906-1910 970-1908 908-1909
906-1909 970-1910 908-1908
906-1909 970-1908 908-1910
906-1908 970-1910 908-1909
906-1908 970-1909 908-1910
Also, your database structure seems a bit strange. The Event Measure table has a structure that is typically used for a many-to-many relationship, but you're trying to encode a one-to-one relationship. Why do you have a separate Event Measure table instead of simply having a foreign key to the event on the measure table?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 10, 2009 at 5:56 am
Thank you guys for your responses, I have resolved it by amending the cursor..
Drew you are rite the eventmeasure table should not be there at all. This will be taken care off going forward. The table is there to hold historic data from the previous version. We are upgrading them, so need to correct the data before migration.
declare @eventid int
declare @identity int
declare evnt CURSOR FOR
select e2.eventid from event e2
where e2.eventid not in
(select
em.eventid
from eventmeasure em inner join event e
on em.eventid=e.eventid
inner join measure m
on em.measureid=m.measureid
where e.eventname='Work Order'
and m.measurename='engWoOpt5')
and e2.eventname='Work Order'
Open evnt
FETCH NEXT fROM evnt into @eventid
While @@FETCH_STATUS=0
BEGIN
INSERT INTO measure
([measureName]
,[measureType]
,[pickListId]
,[displayText]
,[consoleType]
,[hidden]
,[defaultStringValue]
,[defaultIntegerValue]
,[defaultDoubleValue]
,[defaultDateValue]
,[formatMask]
,[maxVal]
,[minVal]
,[booleanOn]
,[booleanOff]
,[gotoUrl]
,[maxChars]
,[charWidth]
,[countUnitId]
,[mandatory]
,[displayOrder]
,[actionType]
,[version])
select top 1
[measureName]
,[measureType]
,[pickListId]
,[displayText]
,[consoleType]
,'1'
,'-'
,[defaultIntegerValue]
,[defaultDoubleValue]
,[defaultDateValue]
,[formatMask]
,[maxVal]
,[minVal]
,[booleanOn]
,[booleanOff]
,[gotoUrl]
,[maxChars]
,[charWidth]
,[countUnitId]
,[mandatory]
,[displayOrder]
,[actionType]
,[version]
from measure where measurename = 'engWoOpt5' and
measureid not in (select measureid from eventmeasure)
select @identity = @@identity
Insert into eventmeasure
select @eventid,@identity
FETCH NEXT fROM evnt into @eventid
END
Close evnt
DEALLOCATE evnt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply