August 22, 2012 at 5:15 pm
I apologize in advance if I'm not posting correctly....
I need to migrate 1 unnormalized table (TempLegacy) into 3 tables: TempContacts, TempPhone, and TempPhoneType.
All legacy phone numbers should be put in TempPhone with a lookup to TempPhoneType. So TempLegacy.Phone1 will get a new record in TempPHone with a link to PhoneType = 1 -- and all TempLegacy.Phone2 values would get PhoneType2, etc. I'm a total beginner, but somewhat adept at figuring things out, so please just point me in the right direction. Do I need to write code with a For Each loop or can I do all this with Sql statements? I tried to figure it out with Integration Services, but got lost. Any help would be greatly appreciated!
---TempLegacy looks like this:
---ContactID LastName Phone1 Phone2 Phone3 Phone4
------------------------
IF OBJECT_ID('TempLegacy','U') IS NOT NULL
DROP TABLE TempLegacy
------- Create the test table with data
CREATE TABLE TempLegacy
(
[ContactID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[LastName] [nvarchar](50) NOT NULL,
[Phone1] [nvarchar](50) NULL,
[Phone2] [nvarchar](50) NULL,
[Phone3] [nvarchar](50) NULL,
[Phone4] [nvarchar](50) NULL,
)
------- Allows the identity feature to autoincrement key
SET IDENTITY_INSERT TempLegacy ON
-------- Insert test data into TempLegacy
INSERT INTO TempLegacy
(ContactID, LastName, Phone1, Phone2, Phone3, Phone4)
SELECT '5576','Greer','303-969-1111','303-969-2222','303-969-3333','303-969-4444' UNION ALL
SELECT '5577','Eckoff','651-290-1111','651-290-2222','NULL','651-290-4444' UNION ALL
SELECT '7479','Sercaz','520-576-1111', 'NULL', 'NULL', 'NULL' UNION ALL
SELECT '7481','Holt','928-468-1111','928-468-2222','928-468-3333','NULL'
---------- Turn Identity feature off
SET IDENTITY_INSERT TempLegacy OFF
---------------------------------
---TempContact looks like this:
---ContactID, LastName
----------------------------
CREATE TABLE TempContacts
(
[ContactID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[LastName] [nvarchar](50) NOT NULL,
)
----------------------------------------
---TempPhone looks like this:
---PhoneID,ContactID,PhoneTypeID,PhoneNumber
CREATE TABLE TempPhone
[PhoneID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ContactID] [int] NULL,
[PhoneTypeID] [tinyint] NULL,
[PhoneNumber] [varchar](25) NULL,
ALTER TABLE [dbo].[tblPhone] WITH CHECK ADD CONSTRAINT [FK_tblPhone_TempPhoneType] FOREIGN KEY([PhoneTypeID])
REFERENCES [dbo].[TempPhoneType] ([PhoneTypeID])
GO
ALTER TABLE [dbo].[tblPhone] CHECK CONSTRAINT [FK_tblPhone_TempPhoneType]
GO
ALTER TABLE [dbo].[tblPhone] WITH CHECK ADD CONSTRAINT [FK_tblPhone_to_tblContacts] FOREIGN KEY([ContactID])
REFERENCES [dbo].[tblContacts2] ([ContactID])
GO
ALTER TABLE [dbo].[tblPhone] CHECK CONSTRAINT [FK_tblPhone_to_tblContacts]
GO
-----------------
---TempPhoneType looks like this:
---PhoneTypeID,PhoneTypeDescription
CREATE TABLE TempPhoneType
(
[PhoneTypeID] tinyint PRIMARY KEY CLUSTERED,
[PhoneTypeDescription] [varchar](20) NULL
)
;
-------- Insert test data into TempPhoneType
INSERT INTO TempPhoneType
(PhoneTypeID, PhoneTypeDescription)
SELECT '1','Office' UNION ALL
SELECT '2','Cell' UNION ALL
SELECT '3','Home' UNION ALL
SELECT '4','Fax'
---------------Eventually get rid of your temp tables.
IF OBJECT_ID('TempLegacy','U') IS NOT NULL
DROP TABLE TempLegacy
IF OBJECT_ID('TempPhone','U') IS NOT NULL
DROP TABLE TempPhone
IF OBJECT_ID('TempPhoneType','U') IS NOT NULL
DROP TABLE TempPhoneType
August 22, 2012 at 6:08 pm
Sample was nearly perfect, but didn't run out of the gates, was missing a parenthesis or two. I also modified it to be using #Temps instead of hard tables to make my personal life a little easier.
You'll find the code you want after the tag --- New Code Here
---#TempLegacy looks like this:
---ContactID LastName Phone1 Phone2 Phone3 Phone4
------------------------
IF OBJECT_ID('tempdb..#TempLegacy','U') IS NOT NULL
DROP TABLE #TempLegacy
IF OBJECT_ID('tempdb..#TempContacts', 'u') IS NOT NULL
DROP TABLE #TempContacts
IF OBJECT_ID('tempdb..#TempPhone', 'u') IS NOT NULL
DROP TABLE #TempPhone
IF OBJECT_ID('tempdb..#TempPhoneType', 'u') IS NOT NULL
DROP TABLE #TempPhoneType
------- Create the test table with data
CREATE TABLE #TempLegacy
(
[ContactID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[LastName] [nvarchar](50) NOT NULL,
[Phone1] [nvarchar](50) NULL,
[Phone2] [nvarchar](50) NULL,
[Phone3] [nvarchar](50) NULL,
[Phone4] [nvarchar](50) NULL,
)
------- Allows the identity feature to autoincrement key
SET IDENTITY_INSERT #TempLegacy ON
-------- Insert test data into #TempLegacy
INSERT INTO #TempLegacy
(ContactID, LastName, Phone1, Phone2, Phone3, Phone4)
SELECT '5576','Greer','303-969-1111','303-969-2222','303-969-3333','303-969-4444' UNION ALL
SELECT '5577','Eckoff','651-290-1111','651-290-2222','NULL','651-290-4444' UNION ALL
SELECT '7479','Sercaz','520-576-1111', 'NULL', 'NULL', 'NULL' UNION ALL
SELECT '7481','Holt','928-468-1111','928-468-2222','928-468-3333','NULL'
---------- Turn Identity feature off
SET IDENTITY_INSERT #TempLegacy OFF
---------------------------------
---TempContact looks like this:
---ContactID, LastName
----------------------------
CREATE TABLE #TempContacts
(
[ContactID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[LastName] [nvarchar](50) NOT NULL,
)
----------------------------------------
---#TempPhone looks like this:
---PhoneID,ContactID,PhoneTypeID,PhoneNumber
CREATE TABLE #TempPhone
([PhoneID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ContactID] [int] NULL,
[PhoneTypeID] [tinyint] NULL,
[PhoneNumber] [varchar](25) NULL
)
--ALTER TABLE [dbo].[tblPhone] WITH CHECK ADD CONSTRAINT [FK_tblPhone_#TempPhoneType] FOREIGN KEY([PhoneTypeID])
--REFERENCES [dbo].[#TempPhoneType] ([PhoneTypeID])
--GO
--ALTER TABLE [dbo].[tblPhone] CHECK CONSTRAINT [FK_tblPhone_#TempPhoneType]
--GO
--ALTER TABLE [dbo].[tblPhone] WITH CHECK ADD CONSTRAINT [FK_tblPhone_to_tblContacts] FOREIGN KEY([ContactID])
--REFERENCES [dbo].[tblContacts2] ([ContactID])
--GO
--ALTER TABLE [dbo].[tblPhone] CHECK CONSTRAINT [FK_tblPhone_to_tblContacts]
--GO
-----------------
---#TempPhoneType looks like this:
---PhoneTypeID,PhoneTypeDescription
CREATE TABLE #TempPhoneType
(
[PhoneTypeID] tinyint PRIMARY KEY CLUSTERED,
[PhoneTypeDescription] [varchar](20) NULL
)
;
-------- Insert test data into #TempPhoneType
INSERT INTO #TempPhoneType
(PhoneTypeID, PhoneTypeDescription)
SELECT '1','Office' UNION ALL
SELECT '2','Cell' UNION ALL
SELECT '3','Home' UNION ALL
SELECT '4','Fax'
---------------Eventually get rid of your temp tables.
--- New Code here
SET IDENTITY_INSERT #TempContacts ON
INSERT INTO #TempContacts
(ContactID, LastName)
SELECT
ContactID, LastName
FROM
#TempLegacy
SET IDENTITY_INSERT #TempContacts OFF
INSERT INTO #TempPhone
( ContactID, PhoneTypeID, PhoneNumber)
SELECT
ContactID, 1, Phone1
FROM
#TempLegacy
UNION ALL
SELECT
ContactID, 2, Phone2
FROM
#TempLegacy
UNION ALL
SELECT
ContactID, 3, Phone3
FROM
#TempLegacy
UNION ALL
SELECT
ContactID, 4, Phone4
FROM
#TempLegacy
SELECT * FROM #TempPhone
IF OBJECT_ID('#TempLegacy','U') IS NOT NULL
DROP TABLE #TempLegacy
IF OBJECT_ID('#TempPhone','U') IS NOT NULL
DROP TABLE #TempPhone
IF OBJECT_ID('#TempPhoneType','U') IS NOT NULL
DROP TABLE #TempPhoneType
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 23, 2012 at 2:58 pm
Thank you! I'm both bummed and pleased you answered this so quickly. It was so easy for you and so hard for me. I understand what you did, but have a few questions. Dumb questions.
1) I started out with the #temp tables before my post, but I couldn't see them in SSMS. When you make the tempdb where does it go? (I know, so dumb.)
2) How do you get the code in that little window? Do you put some html tags around it so it shows up like that with the scroll bar?
3) I'm supposed to give you points or a happy face now, right? Where do I do that?
Thanks again, Ev -- I really appreciate it!
August 23, 2012 at 6:24 pm
saschup (8/23/2012)
Thank you! I'm both bummed and pleased you answered this so quickly. It was so easy for you and so hard for me.
I have more practice, no worries. 🙂
1) I started out with the #temp tables before my post, but I couldn't see them in SSMS. When you make the tempdb where does it go? (I know, so dumb.)
Well, that can get a little complicated. Short version, they exist in tempdb. Long version: Not for very long. A #tmp only exists as long as the connection that called them is open and active, and cannot be seen from another connection. They're used so the same proc code can create temporary structures without stepping on other concurrent calls, they way they would if you used a 'permanent' table that you could see in SSMS. Think of them more as memory structures than physical storage. That's not true, but it'll help you understand them until you dig into them more.
2) How do you get the code in that little window? Do you put some html tags around it so it shows up like that with the scroll bar?
On the right hand side of your post, you'll see a number of IFCode Shortcuts. The one you want is code="sql". 🙂
3) I'm supposed to give you points or a happy face now, right? Where do I do that?
Nope, we're not Experts Exchange or anything like that, just a community board, though I appreciate the offer. However, this:
Thanks again, Ev -- I really appreciate it!
Is my happy face and points. Glad to help. Just make sure you test your sample code next time before posting. :hehe: If you have any questions about the code itself, just ask, I or someone else will be happy to explain it to you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 24, 2012 at 1:41 am
Depending on how many legacy rows you need to convert, you may want to consider this approach to the phone numbers:
INSERT INTO #TempPhone
( ContactID, PhoneTypeID, PhoneNumber)
SELECT
ContactID, n, Phone
FROM
#TempLegacy
CROSS APPLY (
VALUES (1, Phone1)
,(2, Phone2)
,(3, Phone3)
,(4, Phone4)) a(n, Phone)
It might run a tad faster.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 24, 2012 at 10:30 am
Ok -- thanks again for the great explanations and tips. I can't tell you how much it helps!
August 24, 2012 at 11:32 am
Thanks Fishman! This worked snazzily. I really appreciate the help from you and Ev. I can use these tips for all the other migration tasks I have to do. This is one of those projects where I'm forced to learn (and succeed?) under a fast-approaching deadline -- but I know many of you have been there, so it's possible!
August 24, 2012 at 6:05 pm
saschup (8/24/2012)
Thanks Fishman! This worked snazzily. I really appreciate the help from you and Ev. I can use these tips for all the other migration tasks I have to do. This is one of those projects where I'm forced to learn (and succeed?) under a fast-approaching deadline -- but I know many of you have been there, so it's possible!
You're welcome butterfly.
I'll use my new moniker going forward.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply