Inserting into another table using if exists

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 2008 Forums

Transact-SQL (2008)

Inserting into another table using if exists

Author

Topic

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 06:33:29

Hi

I am trying to select a value from one table and insert it into a specfic section of another table. I have the following sql (mssql 2008)

if exists (select datesubmitted from JADHist where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))

Begin

Select JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))

end
	
else

begin

Select jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))
	
end

This always retrieves one value. I have another table which is 2 columns, one is the date with the hour next to it and there is an entry for the next 2 weeks for every hour. The format is 2013-03-26 01:00:00.000 and so on. I want to insert the value I get from the code above into the 2nd table for one 24 hour period (24 entries). THe 24 period will be today from 6am till tomorrow at 6am.

Hope this all makes sense, im just confused how best to go about this.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 06:48:45

--Query for Today 6AM to tomorrow 6AM


;with H24Formatcte(StartDate) as 
(
	SELECT DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE())) -- Today 6 AM
	UNION ALL
	SELECT DATEADD(HH, 1, StartDate) 
	FROM H24Formatcte 
	WHERE StartDate <DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE()+1))
)
SELECT * FROM H24Formatcte

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 07:01:28

thanks for that, I already have the dates and times set up, I just need to be able to update the table through a certain date range with the value obtained from my code.

I have been trying to amend the update to script in mssql to fit in my if exists query but I cant get it right.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 07:07:55

Can you ellaborate the requirement...?
what is the datatype for JadOPNTotal column?

Based on JadOPNTotal column value you want to insert into second table?

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 07:13:12

the datatype of JadOPNTotal is real. the if exists code in my first post gets me that value, I have have the following code with a test value of 10 in it just to prove the code updates opndata correctly.

UPDATE [GasNominations].[dbo].[OPNData]
   SET 
      [Value] = '10'
 WHERE NomSubmittedDate between DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE())) 
 and DATEADD(HH, 5, DATEDIFF(dd, 0, GETDATE()+1))
GO

I want to replace the '10' value with the if exists statement but I cant get the syntax correct.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 09:00:13

[code]
DECLARE @JadOPNTotal real;
if exists (select datesubmitted from JADHist where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))

Begin

Select @JadOPNTotal = JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))
end
else
begin
Select @JadOPNTotal =jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))
end

UPDATE [GasNominations].[dbo].[OPNData]
SET
[Value] = @JadOPNTotal
WHERE NomSubmittedDate between DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE()))
and DATEADD(HH, 5, DATEDIFF(dd, 0, GETDATE()+1))
[/code]

--
Chandu

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 09:31:49

spot on thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 09:33:05