How should I migrate this data into these Sql Server tables?

Question

Hi folks,

I wish to migrate some data from a single table into these new THREE tables.

Here's my destination schema:

Notice that I need to insert into the first Location table .. grab the SCOPE_IDENTITY() .. then insert the rows into the Boundary and Country tables.

The SCOPE_IDENTITY() is killing me :( meaning, I can only see a way to do this via CURSORS. Is there a better alternative?

UPDATE

Here's the scripts for the DB Schema....

Location

CREATE TABLE [dbo].[Locations](
    [LocationId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [OriginalLocationId] [int] NOT NULL,
 CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) 

Country

CREATE TABLE [dbo].[Locations_Country](
    [IsoCode] [nchar](2) NOT NULL,
    [LocationId] [int] NOT NULL,
 CONSTRAINT [PK_Locations_Country] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Locations_Country]  WITH CHECK ADD  CONSTRAINT [FK_Country_inherits_Location] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Locations] ([LocationId])
GO

ALTER TABLE [dbo].[Locations_Country] CHECK CONSTRAINT [FK_Country_inherits_Location]
GO

Boundary

CREATE TABLE [dbo].[Boundaries](
    [LocationId] [int] NOT NULL,
    [CentrePoint] [varbinary](max) NOT NULL,
    [OriginalBoundary] [varbinary](max) NULL,
    [LargeReducedBoundary] [varbinary](max) NULL,
    [MediumReducedBoundary] [varbinary](max) NULL,
    [SmallReducedBoundary] [varbinary](max) NULL,
 CONSTRAINT [PK_Boundaries] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Boundaries]  WITH CHECK ADD  CONSTRAINT [FK_LocationBoundary] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Locations] ([LocationId])
GO

ALTER TABLE [dbo].[Boundaries] CHECK CONSTRAINT [FK_LocationBoundary]
GO

Answer 1

I don't see a need for SCOPE_IDENTITY or cursors if you approach the data in order of the parent/child relationship:

INSERT INTO LOCATION
  SELECT t.name,
         t.originallocationid
    FROM ORIGINAL_TABLE t
GROUP BY t.name, t.originallocationid

INSERT INTO COUNTRY 
SELECT DISTINCT
       t.isocode,
       l.locationid
  FROM ORIGINAL_TABLE t
  JOIN LOCATION l ON l.name = t.name
                 AND l.originallocationid = t.originalocationid

INSERT INTO BOUNDARY
SELECT DISTINCT
       l.locationid,
       t.centrepoint,
       t.originalboundary,
       t.largereducedboundary,
       t.mediumreducedboundary,
       t.smallreducedboundary
  FROM ORIGINAL_TABLE t
  JOIN LOCATION l ON l.name = t.name
                 AND l.originallocationid = t.originalocationid

Answer 2

After loading your Location table you could create a query that joins Location with your source single table. The join criteria would be the natural key (is that the Name column?) and it would return the new LocationId along with the Boundary data. The results would inserted into the new Boundary table.