any ideas to do it without VB ASAP thanks
-
Can't you normalize the design??
Patients?
Diagnos?
Diagid
Patientid
then it's a piece of cake to merge those 2.
-
here is the data that would be a problem with your suggestion --- design change is not in my hands so
key x1 x2 x3 x4 y1 y2 y3 y4
15395 200172NULLNULL131172NULLNULL
2458 1NULLNULLNULL26NULLNULLNULL
11168 209NULLNULLNULL200NULLNULLNULL
10037 21NULLNULLNULL241NULLNULLNULL
9558 1NULLNULLNULL129NULLNULLNULL
3320 1NULLNULLNULL18NULLNULLNULL
7896 4NULLNULLNULL68129NULLNULL
7038 1NULLNULLNULL129NULLNULLNULL
3544 1NULLNULLNULL128NULLNULLNULL
7874 1NULLNULLNULL129NULLNULLNULL
11497 7NULLNULLNULL200NULLNULLNULL
7175 241NULLNULLNULL1NULLNULLNULL
797 1NULLNULLNULL129NULLNULLNULL
13771 2007NULLNULL1NULLNULLNULL
10788 1NULLNULLNULL129NULLNULLNULL
2037 1NULLNULLNULL4NULLNULLNULL
4101 241NULLNULLNULL524NULLNULL
8241 1NULLNULLNULL129NULLNULLNULL
10002 1NULLNULLNULL200129NULLNULL
-
Srry Lunch Break
This is hard to do without a cursor ... so I transalted into 4 updates
HOPE you need to do this only once
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL,
visitnumber INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, null, null,0 UNION ALL
SELECT 12, 2, null, null, null,null UNION ALL
SELECT 14, null, null, null, null,1 UNION ALL
SELECT 15, 2, null, null, null,null UNION ALL
SELECT 16, null, null, null, null,1
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL,
visitnumber INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, 8, null,4 UNION ALL
SELECT 12, null, 3, null, null,7 UNION ALL
SELECT 12, 1, null, 3, null,6 UNION ALL
SELECT 14,null,null,null,null,8
--select * from @f
--select * from @d
DECLARE @Tmp TABLE
(
[Key] INT NULL,
Y int NULL)
INSERT INTO @Tmp
SELECT distinct ,Y from
(select ,Y1 Y FROM @D WHERE Y1 IS NOT NULL UNION ALL
select ,Y2 Y FROM @D WHERE Y2 IS NOT NULL UNION ALL
select ,Y3 Y FROM @D WHERE Y3 IS NOT NULL UNION ALL
select ,Y4 Y FROM @D WHERE Y4 IS NOT NULL ) a1
select * from @Tmp
declare @dummyVal int
SET @dummyVal=-1
UPDATE F
SET
X1=(SELECT top 1 Y FROM @Tmp WHERE Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal)) and =f.)
FROM @F F
WHERE X1 IS NULL
UPDATE F
SET
X2=(SELECT TOP 1 Y FROM @Tmp WHERE Y NOT IN (ISNULL(X1,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal)) and =f.)
FROM @F F
WHERE X2 IS NULL
UPDATE F
SET
X3=(SELECT TOP 1 Y FROM @Tmp WHERE Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X1,@dummyVal),ISNULL(X4,@dummyVal)) and =f.)
FROM @F F
WHERE X3 IS NULL
UPDATE F
SET
X4=(SELECT TOP 1 Y FROM @Tmp WHERE Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X1,@dummyVal)) and =f.)
FROM @F F
WHERE X4 IS NULL
UPDATE F
SET
f.visitnumber=case when isnull(f.visitnumber,0)>isnull(e.visitnumber,0) then isnull(f.visitnumber,0) else isnull(e.visitnumber,0) end
FROM
@F F
LEFT OUTER JOIN
(select ,max(isnull(visitnumber,0)) visitnumber from @D where coalesce(y1,y2,y3,y4) is not null group by ) e
on f.=e.
select * from @f
Kindest Regards,Vasc
-
this seem to work ,, thanks,, i tried to run it on the actual data -- both tabels have aorund 100K rows ,, and its still running after 15 mins -- anything we can do to make it go a bit faster?
-
You need an INDEX on field on that @Tmp table which has to be moved from var to temp table.
You need to do this more than once?
Kindest Regards,Vasc
-
how would i do that,,,,, i only need to do it ONCE ,,thank God
-
Because you have so many rows this should work better
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL,
visitnumber INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, null, null,0 UNION ALL
SELECT 12, 2, null, null, null,null UNION ALL
SELECT 14, null, null, null, null,1 UNION ALL
SELECT 15, 2, null, null, null,null UNION ALL
SELECT 16, null, null, null, null,1
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL,
visitnumber INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, 8, null,4 UNION ALL
SELECT 12, null, 3, null, null,7 UNION ALL
SELECT 12, 1, null, 3, null,6 UNION ALL
SELECT 14,null,null,null,null,8
--select * from @f
--select * from @d
DECLARE @Tmp TABLE
(
[Key] INT NULL,
Y int NULL)
INSERT INTO @Tmp
SELECT distinct ,Y from
(select ,Y1 Y FROM @D WHERE Y1 IS NOT NULL UNION ALL
select ,Y2 Y FROM @D WHERE Y2 IS NOT NULL UNION ALL
select ,Y3 Y FROM @D WHERE Y3 IS NOT NULL UNION ALL
select ,Y4 Y FROM @D WHERE Y4 IS NOT NULL ) a1
select * from @Tmp
declare @dummyVal int
SET @dummyVal=-1
UPDATE F
SET
X1=Y
FROM @F F
INNER JOIN
@Tmp t
on t.=f.
WHERE X1 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X2=Y
FROM @F F
INNER JOIN
@Tmp t
on t.=f.
WHERE X2 IS NULL AND
Y NOT IN (ISNULL(X1,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X3=Y
FROM @F F
INNER JOIN
@Tmp t
on t.=f.
WHERE X3 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X1,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X4=Y
FROM @F F
INNER JOIN
@Tmp t
on t.=f.
WHERE X4 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X1,@dummyVal))
UPDATE F
SET
f.visitnumber=case when isnull(f.visitnumber,0)>isnull(e.visitnumber,0) then isnull(f.visitnumber,0) else isnull(e.visitnumber,0) end
FROM
@F F
LEFT OUTER JOIN
(select ,max(isnull(visitnumber,0)) visitnumber from @D where coalesce(y1,y2,y3,y4) is not null group by ) e
on f.=e.
select * from @f
You can run 1 update at a time without losing or damaging data
Kindest Regards,Vasc
-
I hope your tables have INDEX on FIELD
SET NOCOUNT ON
DECLARE @F TABLE
(
[Key] INT NULL,
X1 INT NULL,
X2 INT NULL,
X3 INT NULL,
X4 INT NULL,
visitnumber INT NULL
)
INSERT @F
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, null, null,0 UNION ALL
SELECT 12, 2, null, null, null,null UNION ALL
SELECT 14, null, null, null, null,1 UNION ALL
SELECT 15, 2, null, null, null,null UNION ALL
SELECT 16, null, null, null, null,1
DECLARE @D TABLE
(
[Key] INT NULL,
Y1 INT NULL,
Y2 INT NULL,
Y3 INT NULL,
Y4 INT NULL,
visitnumber INT NULL
)
INSERT @D
SELECT 1, 12, 1, 2, null,null UNION ALL
SELECT 2, 1, 2, 8, null,4 UNION ALL
SELECT 12, null, 3, null, null,7 UNION ALL
SELECT 12, 1, null, 3, null,6 UNION ALL
SELECT 14,null,null,null,null,8
--select * from @f
--select * from @d
CREATE TABLE #tmpTBL
(
[Key] INT NULL,
Y int NULL)
CREATE CLUSTERED INDEX [IndexTMP] ON [dbo].[#tmpTBL]() WITH FILLFACTOR = 70 ON [PRIMARY]
INSERT INTO #tmpTBL
SELECT distinct ,Y from
(select ,Y1 Y FROM @D WHERE Y1 IS NOT NULL UNION ALL
select ,Y2 Y FROM @D WHERE Y2 IS NOT NULL UNION ALL
select ,Y3 Y FROM @D WHERE Y3 IS NOT NULL UNION ALL
select ,Y4 Y FROM @D WHERE Y4 IS NOT NULL ) a1
select * from #tmpTBL
declare @dummyVal int
SET @dummyVal=-1
UPDATE F
SET
X1=Y
FROM @F F
INNER JOIN
#tmpTBL t
on t.=f.
WHERE X1 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X2=Y
FROM @F F
INNER JOIN
#tmpTBL t
on t.=f.
WHERE X2 IS NULL AND
Y NOT IN (ISNULL(X1,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X3=Y
FROM @F F
INNER JOIN
#tmpTBL t
on t.=f.
WHERE X3 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X1,@dummyVal),ISNULL(X4,@dummyVal))
UPDATE F
SET
X4=Y
FROM @F F
INNER JOIN
#tmpTBL t
on t.=f.
WHERE X4 IS NULL AND
Y NOT IN (ISNULL(X2,@dummyVal),ISNULL(X3,@dummyVal),ISNULL(X1,@dummyVal))
UPDATE F
SET
f.visitnumber=case when isnull(f.visitnumber,0)>isnull(e.visitnumber,0) then isnull(f.visitnumber,0) else isnull(e.visitnumber,0) end
FROM
@F F
LEFT OUTER JOIN
(select ,max(isnull(visitnumber,0)) visitnumber from @D where coalesce(y1,y2,y3,y4) is not null group by ) e
on f.=e.
select * from @f
drop table #tmpTBL
Kindest Regards,Vasc
-
getting this error
Cannot create an index on '#tmpTBL', because this table does not exist in database 'xyz'.
-
err, should work... are you sure that the temp table is still created? Are you in the correct database?
-
yes,, this gives me thousands of rows if i dont try to create index,, so i know temp is created
--CREATE INDEX [IndexTMP] ON [tempdb].[#tmpTBL]() WITH FILLFACTOR = 70 ON [PRIMARY]
select * from @tmpTBL
-
#tmpTBL is a temp table
@tmpTBL is a table variable (who doesn't accept indexes anyways besides the clustered PK).
-
CREATE CLUSTERED INDEX [IndexTMP] ON [tempdb].[#tmpTBL]() WITH FILLFACTOR = 70 ON [PRIMARY]
Kindest Regards,Vasc
-
thanks so much for your help and insight,, i appreciate it very much,, thanks
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply