Let's say I have data in 3 different tables which have different Primary Key column names and data types, here are the table definitions:
CREATE TABLE dbo.Table1
(
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
AddressLine1 VARCHAR(100) NULL,
AddressLine2 VARCHAR(100) NULL,
AddressLine3 VARCHAR(100) NULL,
AddressLine4 VARCHAR(100) NULL,
Postcode VARCHAR(10)
)
GO
CREATE TABLE dbo.Table2
(
UniqueID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
AddressLine1 VARCHAR(100) NULL,
AddressLine2 VARCHAR(100) NULL,
AddressLine3 VARCHAR(100) NULL,
AddressLine4 VARCHAR(100) NULL,
Postcode VARCHAR(10)
)
GO
CREATE TABLE dbo.Table3
(
Reference VARCHAR(10) PRIMARY KEY NOT NULL,
AddressLine1 VARCHAR(100) NULL,
AddressLine2 VARCHAR(100) NULL,
AddressLine3 VARCHAR(100) NULL,
AddressLine4 VARCHAR(100) NULL,
Postcode VARCHAR(10)
)
The above 3 tables are independent of each other. As we can see, the primary key columns' names and data types are different in all of the tables.
What I need to do is integrate the data from the 3 tables above into just one table to carry out a specific process, but I need to later on link the information back to the source tables, so I was thinking of creating a table like this:
CREATE TABLE dbo.DataIntegrated
(
SourceTableName sysname,
SourceTablePK VARCHAR(100),
AddressLine1 VARCHAR(100) NULL,
AddressLine2 VARCHAR(100) NULL,
AddressLine3 VARCHAR(100) NULL,
AddressLine4 VARCHAR(100) NULL,
Postcode VARCHAR(10),
CONSTRAINT PK_DataIntegrated PRIMARY KEY CLUSTERED
(SourceTableName, SourceTablePK)
)
The above table would allow me to integrate data form all of the 3 tables, but given the different Primary Key column data types in Table1, Table2 and Table3 (INT, UNIQUEIDENTIFIER and VARCHAR(10), respectively) I am having to use a VARCHAR column in dbo.DataIntegrated table in order to store the Primary Key value from each source table, which does not look very elegant and can be risky I suppose? Is there a better way of doing this?
Aucun commentaire:
Enregistrer un commentaire