August 3, 2016 at 4:18 pm
IN relation to this thread:
http://www.sqlservercentral.com/Forums/Topic1805751-3077-7.aspx#bm1806866
I have stumbled upon storing tables from an output and it really has to do with how the insert statement handles subqueries.
Here is the data:
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year/
I've exceeded my upload limit
ALTER TABLE [dbo].[hurracane]
DROP COLUMN Basin, Sub_basin, Nature, Center, ISO_time, Serial_num, track_type, Num, [Wind(WMO)], [Pres(WMO)], [Wind(WMO) Percentile], [Pres(WMO) Percentile]
ALTER TABLE [dbo].[hurracane]
DROP COLUMN ISO_TIme
SELECT *
from [dbo].[hurracane]
ALTER TABLE [dbo].[hurracane]
ADD rowid int
WITH cteRank AS
(
SELECT
indexid = ROW_NUMBER() OVER (PARTITION BY [Name], [Season] ORDER BY [Name])
,RowID
FROM [dbo].[hurracane]
)
UPDATE cteRank
SET RowID = indexid
;
SELECT
a.rowid,
a.Season,
a.Name,
a.Latitude,
a.Longitude,
b.rowid,
b.Season,
b.Name,
b.Latitude,
b.Longitude,
CAST('LINESTRING(' + a.Longitude +' '+ a.Latitude +', '+
b.Longitude +' '+ b.Latitude + ')' AS GEOGRAPHY) AS segmentspatialdata
FROM
hurracane a
LEFT OUTER JOIN
hurracane b
ON
b.rowid = a.rowid + 1
AND b.season = a.season
AND b.name = a.name
WHERE
b.rowid IS NOT NULL
ORDER BY a.rowid
I have attached the output. I wish to store segmentspatialdata along with Year, Name into a new table. Can this be done?
August 3, 2016 at 7:57 pm
Does anyone know a good reference for updating tables for left outer joins?
Do I create the column I wish to store before executing the left outer join then populate the column?
THis issue has been solved.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply