July 10, 2009 at 4:52 am
Hi to all,
Suppose i have a table A with the fields (RaID INT, TiID INT, Path VARCHAR(50),
and i want to migrate that information to TableF with the fields (RaKey INT, TiKey INT, PathKey INT),
but each field on TableA have a Table, example for the field RaID INT... we have table RA (RaKey INT, RaID INT), before i insert the RaKey value in table F i have to see if exists that RaID in Table RA, if not exists i insert , if exists i have to know which is the RaKey... this is hard to explain, in T-SQL
will be something like this...
DECLARE @RaIDINT
DECLARE @timeDATETIME
DECLARE @ViewsINT
DECLARE @PathNVARCHAR(512)
DECLARE @RAKeyINT
DECLARE @TiKeyINT
DECLARE @PaKeyINT
DECLARE @QKeyINT
CREATE TABLE #Fact1Temp (
RaKey INT, TiKey INT, PaKey INT, QKey INT, Views INT)
DECLARE csr CURSOR FOR
SELECT RaID, Time, Views, Path FROM stage1 WITH(NOLOCK)
OPEN csr
FETCH NEXT FROM csr
INTO @RaID,@time,@Views,@Path
WHILE @@FETCH_STATUS = 0
BEGIN
--RAKEY
IF EXISTS (SELECT RaKey FROM DimRa WITH(NOLOCK)
WHERE RaID = @RAID)
BEGIN
SELECT@RAKey = RaKey
FROMDimRa WITH(NOLOCK)
WHERERaID = @RAID
END
ELSE
BEGIN
INSERT INTO DimRa (RaID, SystemID)
SELECT @RAID, 1
SET @RAKey = SCOPE_IDENTITY()
END
--TIKEY
IF EXISTS (SELECT TiKey FROM DimTi WITH(NOLOCK)
WHERE [Year] = YEAR(@Time)
AND [Month] = MONTH(@Time)
AND [Day] = DAY(@Time)
AND [Hour] = DATEPART(hh,@Time))
BEGIN
SELECT@TiKey = TiKey
FROMDimTi WITH(NOLOCK)
WHERE[Year] = YEAR(@Time)
AND[Month] = MONTH(@Time)
AND[Day] = DAY(@Time)
AND[Hour] = DATEPART(hh,@Time)
END
ELSE
BEGIN
INSERT INTO DimTi ([Year],[Month],[Day],[Hour])
SELECT YEAR(@Time),MONTH(@Time),DAY(@Time),DATEPART(hh,@Time)
SET @TiKey = SCOPE_IDENTITY()
END
--PAKEY
IF EXISTS (SELECT PaKey FROM DimUrl WITH(NOLOCK)
WHERE PageName = SUBSTRING(@Path,0,PATINDEX('%?%',@Path)))
BEGIN
SELECT@PaKey = PaKey
FROMDimUrl WITH(NOLOCK)
WHEREPageName = SUBSTRING(@Path,0,PATINDEX('%?%',@Path))
END
ELSE
BEGIN
INSERT INTO DimUrl (PageName)
SELECT SUBSTRING(@Path,0,PATINDEX('%?%',@Path))
SET @PaKey = SCOPE_IDENTITY()
END
--QKEY
IF EXISTS (SELECT QKEY FROM DimUrlQ WITH(NOLOCK)
WHERE QueryStringName = SUBSTRING(@Path,PATINDEX('%?%',@Path)+1,LEN(@Path)))
BEGIN
SELECT@QKEY = QKEY
FROMDimUrlQ WITH(NOLOCK)
WHEREQueryStringName = SUBSTRING(@Path,PATINDEX('%?%',@Path)+1,LEN(@Path))
END
ELSE
BEGIN
INSERT INTO DimUrlQ (QueryStringName)
SELECT SUBSTRING(@Path,PATINDEX('%?%',@Path)+1,LEN(@Path))
SET @QKEY = SCOPE_IDENTITY()
END
--FACTVIEWS
INSERT INTO #Fact1Temp
SELECT @RaKey, @TiKey, @PaKey ,@QKey, @Views
FETCH NEXT FROM csr
INTO @RaID,@time,@Views,@Path
END
CLOSE csr
DEALLOCATE csr
INSERT INTO Fact1 (RealEstateKey,TimeKey,PageKey,QueryStringKey,Views)
SELECT Rakey, TiKey, PaKey, QKey, SUM(Views)
FROM #Fact1Temp WITH(NOLOCK)
GROUP BY Rakey, TiKey, PaKey, QKey
DROP TABLE #Fact1Temp
The Problem is that i need this in SSIS 🙂
Thanks
July 10, 2009 at 7:34 am
Hi again,
No one can help me with this? please 🙂
July 10, 2009 at 8:00 am
I think I understand what you are looking for..
Right before you are ready to write to the database use a lookup transform in the dataflow, use it to look up the id you want, this handles the ones that exist, once you know the id you want you can continue on. Now the trickier part, you need to configure the Error Output from the Lookup transform, this is where you will handle the ones that don't exist. I was a little unclear as to what exactly was happening, let me see if I can explain what I think.
1. Data from table a may or may not already have related record in table b.
2. Lookup id or related record in table b, if there is one, if not create that record and use its id.
3. write the record to the database.
Am I close?
CEWII
July 10, 2009 at 8:10 am
Hello, and thanks to answer me and give me nice ideias... 🙂
Yes u are very close of what i need... But like i show u on my script... every row in TableA, have 4 fields that i should see if, in the representative table already exists and if not i have to create it... But i think i should do this at the same time, cause in the destination table i have to make a group by, to insert the days by hour... This is very hard to explain 🙂
But i will look ur ideias right now, and try to make something 🙂
thanks again
July 10, 2009 at 8:11 am
insert the rows* by hour
July 10, 2009 at 8:19 am
My idea really only covers the if not exists go do something else process. You can use the multicast operation to generate two seperate paths for the data..
Not sure, it is hard to understand..
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply