Lookup Date value and update table if match found

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Lookup Date value and update table if match found

Author

Topic

keentolearn
Starting Member

21 Posts

Posted - 2009-07-16 : 12:12:05

Hi,
I need to check date values in two table and insert into third table.

I have the following tables:
LookupDateTime (LookupID,Lookupvalue)as L
MyTable (EXPID,DealDate,DateLookup )as my
MainLookup(MainID, LookUpID)as ml

1. I need to check to see if any of the dates on my table exists on table L and if not create dates in table L then get all the lookupids from L to MyTable.DateLookup column.

The select quey below returns 29 matches which means there are 5 dates whichs doesnt exists in table L and needs to be created.

select distinct a.recordID, a.dealdate
from Drall as a
where dealdate is not null
and exists (Select 1 from LookUpDateTime as b
where CONVERT(VARCHAR(10), LookUPValue, 120)=a.dealdate)

2. Then I will need to :
insert into table ml (MainID, LookUpID)
(Select ExpID, DateLookup from MYTable where DealDate is not null)

I have created this for part 1:
begin tran
declare @row int
declare @date varchar(10)
declare @ident varchar(100)

declare csr_cl cursor for
select a.LookupID
from LookUpDateTime as a

open csr_cl
fetch next from csr_cl into @date
while @@fetch_status = 0
begin
if exists (select 1 from DRALL where DealDate=@date)
begin
insert into DRALL (DateLookUp)
values (@date)
set @ident = (select Ident_Current('LookUpDateTime'))
end

else
begin
set @ident = (select LookUpID from LookupDateTime where
LookUpValue = @date)
end

update MYtable set DateLookUp = @ident where DealDate= @date

fetch next from csr_cl into @date
end
close csr_cl
deallocate csr_cl
rollback tran

this returns the following error msg:
Msg 8170, Level 16, State 2, Line 12
Insufficient result space to convert uniqueidentifier value to char.

Thank you in advance.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2009-07-16 : 13:40:16

It would be helpful to see the datatypes for your tables, although I'd guess LookUpID is a uniqueidentifier.

So without more info I'd guess you need to add an explicit convert.

set @ident = (select Convert(varchar(100), LookUpID) from LookupDateTime where LookUpValue = @date)

keentolearn
Starting Member

21 Posts

Posted - 2009-07-17 : 05:00:53

Hi Joe, many thanks for replying. You are right, LookUpID is a unique identifier. I tried your solution and receive the following error msg at line 12 which is > fetch next from csr_cl into @date

Msg 8170, Level 16, State 2, Line 13
Insufficient result space to convert uniqueidentifier value to char.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2009-07-17 : 09:34:11

Ok. Try changing the size of @date

declare @date varchar(100)

keentolearn
Starting Member

21 Posts

Posted - 2009-07-21 : 12:26:07

I am afraid this still doesnt work. I get the following msg:

Msg 241, Level 16, State 1, Line 23
Conversion failed when converting datetime from character string.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2009-07-21 : 14:46:21

Again, it would be helpful to have the datatypes for the tables.

From what I see you have kind of a datatype mess...
So you're looping through LookUpDateTime and putting the the LookupID (uniqueidentifier?) into @date (varchar).

You're checking for the existance in Drall but you're comparing DealDate (datetime ?) to @date (varchar), but you've put a uniqueidentifier into @date.

If it exists you insert @date (again) and put the Drall identity into @ident (int ?)

If not you put the LookUpID (uniqueidentifier ?) into @ident (int ?)

Then you update MyTable setting DateLookUp (datetime ?) = @ident (int ? but is either int or uniqueidentifier) where DealDate (datetime ?) = @date (varchar, but actually holds a uniqueidentifier)

keentolearn
Starting Member

21 Posts

Posted - 2009-07-22 : 05:55:32

DRALL.Dealdate nvarchar(100),(ExpID,nvarchar(255))
LookUpDateTime(LookUpID(PK,uniqueidentifier), LookUpValue(Datetime)
MainLookUP (LookUpID is PK,uniqueidentifier),(MainID is PK,FK,int)

What i wanted to do is:
loop through LookUpDateTime and DRALL tables, if there is a match(if any values in DRALL.dealdate exists in LookupDateTime.LookUpValue get the LookUPID from LookUpDateTime and ExpID from DRALL into MainLookup (LookupID,MainID)-this is to associate the records on the database with relevant date values.
If DealDate value doesn't exist in lookUPDateTime, create LookUpValue and then get the LookUpID form LookUpdateTime and ExpID from DrALL into MainLookUp(LookUpID,MainID).

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2009-07-22 : 09:46:11

So if I understand correctly you want to:
If records exist in both Drall and LookUpDateTime
then you want to insert the ExpID and DateLookUp values into MainLookUp
If the record exists in Drall, but not LookUpDateTime
then you want to insert the DateLookUp and DealDate into LookUpDateTime
you also want to put the ExpID and DateLookUp values into MainLookUp (which is the same as #1)

--To simplify the process you really want to do this:
-- 1. Insert any records that exist in Drall, but not in LookUpDateTime
Insert Into LookUpDateTime
(LookUpID
, LookUpValue)
Select
NewID()
, Convert(datetime, D.DealDate)
From Drall D
Left Outer Join LookUpDateTime L
On D.DealDate = Convert(varchar(100), L.LookUpValue, 120)
Where L.LookUpID Is Null

-- 2. Insert the values from Drall and LookUpDateTime into MainLookUp
Insert Into MainLookUp
(MainID
, LookUpID)
Select
D.ExpID
, L.LookUpID
From Drall D
Inner Join LookUpDateTime L
On D.DealDate = Convert(varchar(100), L.LookUpValue, 120)

That should do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources