TSQL -将来自不同来源的数据( ...refactoring、PK和FKs )结合在一起。

Question

我有不同的办事处和一个中央总部。每个office都有自己的Server 2008实例，因此每个office都有自己的数据集和自己的ID集。

每个办事处都已经将数据导入总部，并将数据存储在一组类似于此的STAGING_Tables上。

DECLARE @STAGING_COUNTRY TABLE
(
  Original_CountryID INT NOT NULL, 
  OfficeID VARCHAR(10) NOT NULL,
  Data VARCHAR(200) NOT NULL
);

DECLARE @STAGING_CITY TABLE
(
  Original_CityID INT NOT NULL, 
  Original_CountryID_FK INT NOT NULL, 
  OfficeID VARCHAR(10) NOT NULL,
  OtherData VARCHAR(100) NOT NULL
);

STAGING_COUNTRY具有每一行的原始ID (由于每个办事处在其Country表上的第一行都有ID=1 )，而且还有一个惟一的OfficeID值，与Original_CountryID ..makes一起使用的是一个唯一的值。

STAGING_CITY还具有每一行的原始ID，表示每个office的唯一OfficeID值，在本例中是对CountryID的FK (当然，在这一点上，我们可以与OfficeID一起引用Original_CountryID ..that )。

让我们添加一些虚拟行：

/* ADD DUMMY VALUES TO STAGING_COUNTRY */ 
INSERT INTO @STAGING_COUNTRY
(Original_CountryID, OfficeID, Data) VALUES  (1, 'Office1', 'USA')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (2, 'Office1', 'Canada')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (3, 'Office1', 'Japan')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (1, 'Office2', 'USA')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (1, 'Office2', 'Italy')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (3, 'Office2', 'Canada')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (3, 'Office3', 'Canada')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (2, 'Office3', 'France')

INSERT INTO @STAGING_COUNTRY (Original_CountryID, OfficeID, Data)
VALUES  (3, 'Office3', 'USA')



/* ADD DUMMY VALUES TO STAGING_CITY */  
INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK, OfficeID, OtherData) VALUES 
(1, 1, 'Office1', 'New York')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (2, 1, 'Office1', 'Vancouver')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (3, 1, 'Office1', 'Tokia')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (1, 2, 'Office2', 'New York')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (2, 2, 'Office2', 'Rome')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (3, 2, 'Office2', 'Vancouver')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (1, 3, 'Office3', 'Vancouver')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (2, 3, 'Office3', 'Paris')

INSERT INTO @STAGING_CITY (Original_CityID, Original_CountryID_FK,
OfficeID, OtherData) VALUES  (3, 3, 'Office3', 'New York')

中央总部希望从一个中央dtabase运行报告，该数据库几乎包含来自所有办事处的所有数据，但是为了优化这个reporting，我们需要对STAGING_Tables ...and对FINAL_Tables中的数据进行一些重组，如下所示：

DECLARE @FINAL_COUNTRY TABLE
(
  CountryID INT IDENTITY PRIMARY KEY, 
  Original_CountryID INT NOT NULL, 
  OfficeID VARCHAR(10) NOT NULL,
  Data VARCHAR(200) NOT NULL
);

DECLARE @FINAL_CITY TABLE
(
  CityID INT IDENTITY PRIMARY KEY, 
  Original_CityID INT NOT NULL, 
  CountryID_FK INT NOT NULL, 
  OfficeID VARCHAR(10) NOT NULL,
  OtherData VARCHAR(100) NOT NULL
);

问题：

为了报告的目的，FINAL_COUNTRY和FINAL_CITY表应该尽可能优化。这些报告将用T存储过程编写.

问题：

重组FINAL_Tables的最佳方法是什么，以便每个记录都有一个真正的PK标识符(如原始Office_Tables中的那样)，并且更新每个FK以指向正确的新创建的PK ...at服务器级别？

注：

请注意，在服务器上，两个暂存表和终表都位于同一个DB中。此外，为了其他目的，我们仍然需要将OriginalID保存在FINAL_Tables上。

目标：

这里的主要目标是将表重组为一组表，这些表可以很容易地进行索引以达到性能目的。

如果需要，请询问更多信息。

非常感谢你.

Answer 1

这可能只是一个部分的答案。您可能需要考虑在每个暂存表上添加一个通用标识id。类似于：

DECLARE @STAGING_COUNTRY TABLE 
( 
  Stage_Country_id INT IDENTITY(1,1) NOT NULL,
  Original_CountryID INT NOT NULL,  
  OfficeID VARCHAR(10) NOT NULL, 
  Data VARCHAR(200) NOT NULL 
); 

DECLARE @STAGING_CITY TABLE 
( 
  Stage_City_id INT IDENTITY(1,1) NOT NULL,
  Original_CityID INT NOT NULL,  
  Original_CountryID_FK INT NOT NULL,  
  OfficeID VARCHAR(10) NOT NULL, 
  OtherData VARCHAR(100) NOT NULL 
); 

您的最后一个表不应该有original_ids，因为它们中每个城市/国家只应该有1条记录。

那么我想你需要一些交叉参考表来把你的最后一张桌子和你的舞台桌子连接起来。看起来是这样的：

DECLARE @COUNTRY_xref TABLE          
( 
  country_xref_id INT IDENTITY(1,1) not null,
  CountryID INT not null,
  Stage_Country_id INT             
);          

DECLARE @CITY_xref TABLE          
(          
  city_xref_id INT IDENTITY(1,1) not null,
  CityID INT not null,           
  Stage_City_id INT not null        
);

您是否还在问加载/转换过程会是什么样子，还是更多的是关于模式？

您的最后一张表可能如下所示：

DECLARE @FINAL_COUNTRY TABLE 
( 
  CountryID INT IDENTITY PRIMARY KEY, 
  Data VARCHAR(200) NOT NULL 
); 

DECLARE @FINAL_CITY TABLE 
( 
  CityID INT IDENTITY PRIMARY KEY, 
  CountryID_FK INT NOT NULL,
  OtherData VARCHAR(100) NOT NULL 
);