October 10, 2012 at 11:17 am
I have an application and database that I inherited, and am now trying to normalize the database for future application development. Currently, I have a table (Original) that I want to convert over to 2 tables with a parent-child relationship (Parent and Child). This hopefully be a one time import, then I think the proper step is to create a trigger on the Original so that anytime a record is added to the Original table, it also creates the record in the Parent and Child tables.
The stucture of the Original table is like:
CREATE TABLE [dbo].Original(
SSN [char](9) NOT NULL,
FirstName [char](25) NULL,
LastName [char](25) NULL,
Address [char](30) NULL,
City [char](25) NULL,
State [char](2) NULL,
Zip [char](9) NULL,
CONSTRAINT [PK_Original] PRIMARY KEY CLUSTERED
( SSN )
)
--Test Data
INSERT INTO [dbo].[Original]
([SSN]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[Zip])
VALUES
('10000001','John', 'Smith', '123 Main', 'Anytown', 'CA', '10000' )
,('10000002','Jane', 'Smith', '123 Main', 'Anytown', 'CA', '10000' )
,('999119999','Mary', 'Souza', '546 4th Street', 'Another Town', 'CA', '10001' )
,('999229999','Mike', 'Jones', '19 Rural Road', 'Anytown', 'CA', '10000' )
I have created 2 new tables:
CREATE TABLE [dbo].[Parent](
[ParentID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](25) NOT NULL,
[FirstName] [varchar](25) NOT NULL,
Address [varchar](30) NULL,
City [varchar](25) NULL,
State [char](2) NULL,
Zip [char](9) NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[ParentID] ASC
))
CREATE TABLE [dbo].[Child](
[ChildID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
--DocumentType will allow for multiple document types, but for this exercise, it will be set to 1 (for SSN in related table)
[DocumentType] [int] NOT NULL,
[DocumentNumber] [varchar](25) NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildID] ASC
))
I am having trouble selecting the fields from Original and inserting them into the Parent, then getting the new ParentID, and inserting that into the Child record with the appriate data from Original.
My expected results would be like:
Original - 100000001, John, Smith, 123 Main, Anytown, CA, 10000
100000002, Jane, Smith, 123 Main, Anytown, CA, 10000
999119999, Mary, Souza, 546 4th Street, Another Town, CA, 10001
999229999, Mike, Jones, 19 Rural Road, Anytown, CA, 10000
Parent -
1, Smith, John, 123 Main, Anytown, CA, 10000
2, Smith, Jane, 123 Main, Anytown, CA, 10000
3, Souza, Mary, 546 4th Street, Another Town, CA, 10001
4, Jones, Mike, 19 Rural Road, Anytown, CA, 10000
Child -
1, 1, 1, 100000000
2, 2, 1, 100000001
3, 3, 1, 999119999
4, 4, 1, 999229999
Thank you for your help
October 15, 2012 at 6:10 am
The easiest and fastest way to get it done, is to add temporary SSN column into Parent table, so you can keep correlation between new generated ParentId and existing SSN. After use you can drop it.
ALTER TABLE dbo.Parent ADD SSN CHAR(9)
INSERT dbo.Parent
SELECT FirstName, LastName, Address, City, State, Zip, SSN
FROM dbo.Original
INSERT dbo.Child
SELECT ParentId, 1, SSN
FROM dbo.Parent
ALTER TABLE dbo.Parent DROP COLUMN SSN
SELECT * FROM dbo.Parent
SELECT * FROM dbo.Child
October 16, 2012 at 5:25 am
The technique Eugene relayed will work great, especially if you do not have a unique key on the original table. However if you do have a unique key and it is not the SSN, an alternate option is to use the OUTPUT clause on the INSERT into the Parent table to return the new ParentID along with that unique key (assuming it is not the SSN) and capture the results to a temporary table. You can then join the temporary table to the original table to pickup the SSN for insert into the child table with the ParentID.
About the Child table, that DDL looks suspiciously similar to an attribute-value table. "Child" sounds like it is masquerading as an "Entity" (or specifically a "Document" in your case), "DocumentType" as "Attribute" and "DocumentNumber" as "Value." I am urging you to re-evaluate your approach. If you want to separate SSN into a different table so it can be secured as an encrypted column, otherwise obfuscated in the schema, or placed in a different filegroup for data management reasons those are great reasons to move it out of the Parent table. However if you're looking for a generic place to drop similar data and partition it using a "document type" then I think you will quickly find the approach devolve as more attributes (DocumentTypes) are added to your "schema" and you are required to maintain data integrity and deliver data in a tabular format once again.
Your model appears more as "attribute-value", a cousin of the EAV model, but the pitfalls are much the same.
Keeping It Simple: EAV FAIL by Bill Karwin
I would leave the SSN in the parent table, or if you need to separate it into a new table that's OK but continue to maintain it as a column named SSN with an appropriate data type.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 16, 2012 at 7:54 am
Gosh, that handle seems familiar. What company do you work for?
--Jeff Moden
October 16, 2012 at 9:25 am
Thank you Eugene. I think I briefly looked at a solution like this, but then focused on @@identity and Scope_Identity, which doesn't appear to work for bulk input. Trial and error learning at it's best.
November 12, 2012 at 8:31 am
Maybe this'll help.
INSERT INTO Parent
SELECT LastName, FirstName, Address, City, State, Zip
FROM Original
INSERT INTO Child
SELECT P.ParentID, 1 DocType, O.SSN
FROM Parent P
INNER JOIN [Original] O
ON P.[FirstName] = O.[FirstName]
AND P.LastName = O.LastName
AND P.Address = O.Address
AND P.City = O.City
AND P.State = O.State
AND P.Zip = O.Zip
SELECT * FROM Child
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply