July 2, 2010 at 8:47 am
Hello, I am working with SQLserver 2008 and trying to convert my insert statement into a update statement. Also, data is being pulled from a table called dbo.GA, which gets its data from sensors - I'm not sure if a placeholder (?) should be used or how. I appreciate any feedback!
USE [Barrow]
GO
/****** Object: StoredProcedure [dbo].[Weather2_Insert] Script Date: 07/01/2010 10:10:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: AR
-- Create date: 01/07/10
-- Description:Update the Wind0
-- =============================================
CREATE PROCEDURE [dbo].[Weather2_Insert]
as
INSERT INTO dbo.Weather2
(datetime, latitude, longitude, temperature , humrel, dew, heatindex,cloudheight, humabs, uvindex,
radiationrel ,wdirection,wgustspeed, wspeed,
wchill, rainrate, raintotal)
SELECT CONVERT(datetime,LEFT(A.item,8) + ' ' + SUBSTRING(A.item,9,2) +':'+SUBSTRING(A.item,11,2) +':'
+ RIGHT(A.item,2)), Cast(B.item as numeric(38,8)), Cast(C.item as numeric(38,8))
,Cast(F.item as numeric(38,8))
,Cast(G.item as numeric(38,8)),Cast(H.item as numeric(38,8)), Cast(I.item as numeric(38,8))
,Cast(J.item as numeric(38,8)),Cast(K.item as numeric(38,8)), Cast(L.item as numeric(38,8))
,Cast(M.item as numeric(38,8)),Cast(N.item as numeric(38,8)),Cast(O.item as numeric(38,8))
,Cast(P.item as numeric(38,8)),Cast(Q.item as numeric(38,8)), Cast(R.item as numeric(38,8))
,Cast(S.item as numeric(38,8))
FROM dbo.GA A
CROSS JOIN dbo.GA B
cross join dbo.GA C
cross join dbo.GA F
cross join dbo.GA G
cross join dbo.GA H
cross join dbo.GA I
cross join dbo.GA J
cross join dbo.GA K
cross join dbo.GA L
cross join dbo.GA M
cross join dbo.GA N
cross join dbo.GA O
cross join dbo.GA P
cross join dbo.GA Q
cross join dbo.GA R
cross join dbo.GA S
WHERE (A.timeframe = 'actual' and A.sensor = 'date0' and A.cat = 'date' and A.unit = 'utc')
And (B.timeframe = 'actual' and B.sensor = 'station' and B.cat = 'latitude' and B.unit = 'decimal')
And (C.timeframe = 'actual' and C.sensor = 'station' and C.cat = 'longitude' and C.unit = 'decimal')
And (F.timeframe = 'actual' and F.sensor = 'th1' and F.cat = 'temp' and F.unit = 'c')
And (G.timeframe = 'actual' and G.sensor = 'th1' and G.cat = 'hum' and G.unit = 'rel')
And (H.timeframe = 'actual' and H.sensor = 'th1' and H.cat = 'dew' and H.unit = 'c')
And (I.timeframe = 'actual' and I.sensor = 'th1' and I.cat = 'heatindex' and I.unit = 'c')
And (J.timeframe = 'actual' and J.sensor = 'th1' and J.cat = 'cloudheight' and J.unit = 'm')
And (K.timeframe = 'actual' and K.sensor = 'th1' and K.cat = 'hum' and K.unit = 'abs')
And (L.timeframe = 'actual' and L.sensor = 'uv0' and L.cat = 'index' and L.unit = '')
And (M.timeframe = 'actual' and M.sensor = 'sol0' and M.cat = 'radiation' and M.unit = 'rel')
And (N.timeframe = 'actual' and N.sensor = 'wind0' and N.cat = 'dir' and N.unit = 'deg')
And (O.timeframe = 'actual' and O.sensor = 'wind0' and O.cat = 'gustspeed' and O.unit = 'ms')
And (P.timeframe = 'actual' and P.sensor = 'wind0' and P.cat = 'speed' and P.unit = 'ms')
And (Q.timeframe = 'actual' and Q.sensor = 'wind0' and Q.cat = 'chill' and Q.unit = 'c')
And (R.timeframe = 'actual' and R.sensor = 'rain0' and R.cat = 'rate' and R.unit = 'mm')
And (S.timeframe = 'actual' and S.sensor = 'rain0' and S.cat = 'total' and S.unit = 'mm')
GO
July 21, 2010 at 6:24 pm
This is really inefficient. If you are still looking for help, provide a table definition and some sample data as well as an example of the data you would want to return from your select. If you have any questions see the link in my signature below.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply