CODEWITHSUNDEEP
sql-server
Joel R.
Joel R.

Reputation: 189

SQL Server: How would I normalize data tables?

I have a table with 3 columns that need to be moved to another table and replaced with the IdentityID of the inserted row in the other table.

Here is the SQLFiddle of the basic idea I'm trying to work with:

CREATE TABLE RECORD 
(
  ID int,
  ObjectDetail_A int,
  ObjectDetail_B int,
  ObjectDetail_C int,
  ObjectDetailID int
)

CREATE TABLE OBJECTDETAIL
(
  ID int,
  ObjectDetail_A int,
  ObjectDetail_B int,
  ObjectDetail_C int
)

So in the end, fields ObjectDetail_A, ObjectDetail_B, and ObjectDetail_C are replaced with the ID of the OBJECTDETAIL table when the data is inserted into that table.

I'm just a little confused into how to insert a row into destination table and then update the source table with the destination identity.

Any help would be much appreciated,

Thanks

Upvotes: 1

Views: 4918

Answers (4)

Joel R.
Joel R.

Reputation: 189

I guess the solution wasn't as complicated as I may have thought.

What I did was:

ALTER TABLE OBJECTDETAIL
ADD SourceID INT
GO

INSERT INTO ProductDetail (ObjectDetail_A, ObjectDetail_B, ObjectDetail_C, SourceID)
SELECT ObjectDetail_A, ObjectDetail_B, ObjectDetail_C, ID
FROM RECORD
GO

UPDATE RECORD
SET RECORD.ObjectDetailID = OBJECTDETAIL.ID
JOIN OBJECTDETAIL
ON RECORD.ID = OBJECTDETAIL.SourceID
GO

ALTER TABLE OBJECTDETAIL
DROP COLUMN SourceID
GO

This is what worked out for me. Thanks for all the replies.

Upvotes: 1

criticalfix
criticalfix

Reputation: 2870

Make OBJECTDETAIL.ID an autoincrement IDENTITY field.

INSERT INTO OBJECTDETAIL
( SELECT DISTINCT 
ObjectDetail_A,
ObjectDetail_B,
ObjectDetail_C
FROM RECORD )

Then update RECORD.ObjectDetailID.

UPDATE R
SET R.ObjectDetailID = OD.ID
FROM RECORD R
JOIN OBJECTDETAIL OD
ON OD.ObjectDetail_A = R.ObjectDetail_A
AND OD.ObjectDetail_B = R.ObjectDetail_B
AND OD.ObjectDetail_C = R.ObjectDetail_C

The RECORD.ObjectDetailID should be a foreign key field, and the RECORD.ObjectDetail_A etc fields should be removed from the table.

Upvotes: 0

Manoj Purohit
Manoj Purohit

Reputation: 3453

As Far as I have understood your problem, you need to create two table..

 CREATE TABLE OBJECTDETAIL
    (
      ObjectDetailID int
      ObjectDetail_A int,
      ObjectDetail_B int,
      ObjectDetail_C int,
    PRIMARY KEY (ObjectDetailID),

    )
    CREATE TABLE RECORD 
    (
      ID int,
      ObjectDetailID int, 
      CONSTRAINT fk_objectdetail FOREIGN KEY (ObjectDetailID) REFERENCES OBJECTDETAIL(ObjectDetailID)
    )

In this way you can first insert in OBJECTDETAIL and then can insert into RECORD using the inserted ObjectDetailID

Upvotes: 0

David
David

Reputation: 34543

As long as the A, B, & C fields are unique or you are okay with the duplicates all using the same ObjectDetail.ID, then you can just insert them all, and then JOIN to get the new ID:

INSERT INTO ObjectDetail
  ( ObjectDetail_A, ObjectDetail_B, ObjectDetail_C )
SELECT DISTINCT ObjectDetail_A, ObjectDetail_B, ObjectDetail_C
FROM Record

UPDATE Record
SET ObjectDetailID = d.ID
FROM Record r
INNER JOIN ObjectDetail d
  ON r.ObjectDetail_A = d.ObjectDetail_A
  AND r.ObjectDetail_B = d.ObjectDetail_B
  AND r.ObjectDetail_C = d.ObjectDetail_C

Upvotes: 0

Related Questions