How pull data from 2 tables that have Uniqueidentifier's as PK's and populate the data into 2 new tables that have AutoIncremented ID's as PK's?

Question

I have a few tables that contain ID's that are of type GUID, pretty much every table is using GUID's instead of incremented IDs, but thats neither here nor their, just a scope of what I am dealing with.

So I have a table called MaterialType and MaterialSubType

the MaterialType is built as the following

MaterialTypeID (PK, Uniqueidentifier, not null)
MaterialType (varchar(40), not null)
Code (varchar(100), not null)
EnabledInd (tinyint, not null)

The MaterialSubType is built as follows

MaterialSubTypeID (PK, Uniqueidentifier, not null)
MaterialTypeID (PK, Uniqueidentifier, not null)
MaterialSubType (varchar(40), not null)
Code (varchar(100), not null)
EnabledInd (tinyint, not null)

The problem I am running into is that I have two updated tables that are pretty much identical, other than the fact is that I am using autoincremented ID's, I need to figure out how to query the data from the original tables and insert the data into the new tables.

I know I can do an "insert into select" to the tables, but that doesn't (at least to my knowledge) help me because I need to have a foreign key in the MaterialSubType to the MaterialType.

So I am not sure how this should be done or how to do it, reason being is that I am not an expert with SQL or a DBA.

I guess the FK is on column `MaterialTypeID`? Please state explicitly so no assumptions are made. Do you need to do this regularly or is it a one off? are both tables updated? Does it need to be updated back the other way if so? The basic approach is to insert new records in `MaterialType` that generate new ID's, but you need to preserve the original GUID so you can use that to 'back reference' and assign the FK's in `MaterialSubType` before inserting them. You'd do this either through a staging table or by permanently adding a 'source reference key' column in your target table. — Nick.Mc, Jul 07 '16 at 00:19
@Nick.McDermaid, to answer your questions... Yes the FK column is the MaterialTypeID, Its a one off, never to happen again. The database that I am working with has all PK's as uniqueIdentifiers and I am changing things around to use Auto Incremented ID's. What I have started trying is creating temp tables, passing the info needed and then using a where statement, if that makes sense? — Chris, Jul 07 '16 at 00:31
I'd use `OUTPUT` clause of the `MERGE` statement to establish a mapping between old and new IDs as I've shown in the [answer](http://stackoverflow.com/a/27498204/4116017) to a similar question. — Vladimir Baranov, Jul 07 '16 at 09:07

score 1 · Answer 1 · answered Jul 07 '16 at 01:16

Again I'm making assumptions:

The code is the universal identifier that you're using to identify new records in both tables
The autoincrement identity values are on MaterialType.MaterialTypeID and MaterialSubType.MaterialSubTypeID (though it doesn't really make sense if you have a PK on two columns when only one would be required in this case)
The databases are on the same SQL Server

First add new records to MaterialType so we can generate a key:

INSERT INTO TargetDB..MaterialType (MaterialType, Code, EnabledInd)
SELECT MaterialType, Code, EnabledInd
FROM SourceDB..MaterialType SRC
WHERE NOT EXISTS (
  SELECT * FROM  TGT
  WHERE TGT.Code = SRC.Code
)

Now add new records to MaterialSubType. To get the target FK value we need to first look up the code value in the source, then use that to lookup the correct FK value in the target.

I strongly suggest you first just run the select without the insert and test some records manually.

INSERT INTO TargetDB..MaterialSubType (
MaterialTypeID,
MaterialSubType,
Code,
EnabledInd
)
SELECT
-- We get this FK value by first looking up the code in the source 
-- then using that to look up the FK in the target
MT.MaterialTypeID,
SRC.MaterialSubType,
SRC.Code,
SRC.EnabledInd
FROM 
SourceDB..MaterialSubType SRC
INNER JOIN
-- Join to source lookup to find the code
SourceDB..MaterialType FK
ON SRC.MaterialTypeID = FK.MaterialTypeID
-- Now we have the source code, look up the target to get the target FK
INNER JOIN
TargetDB..MaterialType MT
ON FK.Code = MT.Code
WHERE NOT EXISTS (
    SELECT *
    FROM TargetDB..MaterialSubType TGT
    SRC.Code = TGT.Code
    )

There might be some errors here. If there are, and you would like them fixed please also confirm my assumptions.

How pull data from 2 tables that have Uniqueidentifier's as PK's and populate the data into 2 new tables that have AutoIncremented ID's as PK's?

1 Answers1