sql - 我应该如何将这些数据迁移到这些 Sql Server 表中？

Question

我希望将一些数据从单个表迁移到这三个新表中。

这是我的目标架构:

请注意，我需要插入第一个 Location 表.. 获取 SCOPE_IDENTITY() .. 然后将行插入 Boundary 和国家/地区 表。

SCOPE_IDENTITY() 快要死了:(意思是，我只能通过 CURSORS 找到一种方法来做到这一点。有更好的选择吗？

更新

这是数据库架构的脚本......

地点

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]
) 

国家

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

边界

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

如果您按照父/子关系的顺序处理数据，我认为不需要 SCOPE_IDENTITY 或游标:

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