I have 2 Tables
Table 1 has
Id
Type
Table 2 has
Id
Type
PartNumber
I need to migrate data from Table 1 to Table 2 based on Type.
Type can be 1 or 2.
Example of Source.
Table 1 has
Id Type
1 1
2 1
3 1
3 2
Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different.
Example of Destination.
Id Type PartNumber
1 1 10
1 2 10
2 1 11
2 2 11
3 1 12
3 2 13
================================
As you see above
1. I need to duplicate records which had just one type in the Source table.
1 1 10
1 2 10
I will have 2 (1 and 2) types now, but everything else the same for the same id.
In my case I will have Part Numbers as guid. I would create them on my own. They will be the same here.
2. I need to have 2 records as it was before in Source table for id's with 2 types, but with different Part Numbers.
3 1 12
3 2 13
In my case I will have Part Numbers as guid. I would create them on my own.They will be different here.
How do I create 2 records for each Id and certain rules for them? How do I find if there is one or there are two records in Source and how do I apply my rules in looping through records? What control in SSIS tool can I use?
Thanks.
This looks like a job for T-SQL, not SSIS. If you can elaborate on the "Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different" statement, I may be able to help more. The logic you describe here isn't precise enough to implement (or perhaps I'm just not following it correctly) but this looks like a simple INSERT .. SELECT to me.|||Can you give me more details? I think you understand correctly.
Look at my examples. It describes what I need.
Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.
In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.
Please let me know if you have more questions.
|||
Vita wrote:
Can you give me more details? I think you understand correctly.
Look at my examples. It describes what I need.
Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.
In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.
Please let me know if you have more questions.
"Part Number will be different" is what was unclear to me. Different in what way? In each case, what should it be?
I think your most recent post provides the needed clarity - let me play with it for a while...
|||Ok... that was more annoying than I figured - it was the NEWID() function that was giving me trouble, so I needed to use a temp table to get the desired results. Here's the deal:
Code Snippet
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
DROP TABLE [dbo].[Table1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))
DROP TABLE [dbo].[Table2]
GO
CREATE TABLE Table1
(
Id INT NOT NULL,
Type INT NOT NULL
)
go
CREATE TABLE Table2
(
Id INT NOT NULL,
Type INT NOT NULL,
PartNumber UNIQUEIDENTIFIER NULL
)
GO
INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table1 VALUES (2, 1)
INSERT INTO Table1 VALUES (3, 1)
INSERT INTO Table1 VALUES (3, 2)
GO
WITH IdExistsOnceCTE
AS
(
SELECT Id
,MIN (Type) AS Type
,NEWID() AS PartNumber
FROM Table1
GROUP BY Id
HAVING COUNT (Type) = 1
)
SELECT IdExistsOnceCTE.*
INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!
FROM IdExistsOnceCTE;
WITH IdExistsTwiceCTE
AS
(
SELECT Id
,Type
,NEWID() AS PartNumber
FROM Table1
WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )
),
DummyDuplicationCTE
AS
(
SELECT 1 AS Dup
UNION ALL
SELECT 2
)
INSERT INTO Table2
SELECT #ExistsOnce.*
FROM #ExistsOnce
CROSS JOIN DummyDuplicationCTE
UNION ALL
SELECT *
FROM IdExistsTwiceCTE
ORDER BY 1, 2;
SELECT *
FROM Table2;
The final SELECT statement is a verification of the contents of Table2, and based on the inputs you provided, produces this output:
Code Snippet
Id Type PartNumber
-- --
1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
3 1 38070FA6-0805-45C3-8503-58F8A9EDF473
3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB
Does this give you what you need?
|||Thank you for your hard work.
The only thing I see wrong is the type.
You got
Id Type PartNumber
-- --
1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
3 1 38070FA6-0805-45C3-8503-58F8A9EDF473
3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB
and I need
Id Type PartNumber
-- --
1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
1 2 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D
2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
2 2 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1
3 1 38070FA6-0805-45C3-8503-58F8A9EDF473
3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB
Type always needs to be 1 and 2.
Everything else looks great.
|||Here you go:
Code Snippet
WITH IdExistsOnceCTE
AS
(
SELECT Id
,MIN (Type) AS Type
,NEWID() AS PartNumber
FROM Table1
GROUP BY Id
HAVING COUNT (Type) = 1
)
SELECT IdExistsOnceCTE.*
INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!
FROM IdExistsOnceCTE;
WITH IdExistsTwiceCTE
AS
(
SELECT Id
,Type
,NEWID() AS PartNumber
FROM Table1
WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )
),
DummyDuplicationCTE
AS
(
SELECT 1 AS Dup
UNION ALL
SELECT 2
)
INSERT INTO Table2
SELECT Id
,Dup
,PartNumber
FROM #ExistsOnce
CROSS JOIN DummyDuplicationCTE
UNION ALL
SELECT *
FROM IdExistsTwiceCTE
ORDER BY 1, 2;
SELECT *
FROM Table2;
This yields:
Code Snippet
Id Type PartNumber
-- --
1 1 7578AB32-6F38-4D23-887B-D81DD9D71DBC
1 2 7578AB32-6F38-4D23-887B-D81DD9D71DBC
2 1 F554FFA0-B0DB-4C53-9D3F-181678617A9B
2 2 F554FFA0-B0DB-4C53-9D3F-181678617A9B
3 1 165DEB39-D6DA-4DD3-B577-DD295BAF7455
3 2 55389A91-EAF6-4360-BFAA-10032EA1AB67
|||Thank you very much!
No comments:
Post a Comment