时间戳

首页 > 解决方案 > 比较不同表的两列的数据类型

sql - 比较不同表的两列的数据类型

问题描述

我有两个不同的表 A 和 B，并且想将数据从表 A 复制到表 B，但只有在匹配两列的数据类型和键约束（如主键）之后。如果数据类型和键约束匹配，则仅将数据从表 A 的列复制到表 B 的同一列，否则将该数据复制到第三个表，即 error_log 表，并在 errorvalue 列中提及错误。

我正在检查数据类型如下：

SELECT C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
FROM SYS.COLUMNS C
WHERE C.OBJECT_ID=OBJECT_ID('table A');

表结构如下所示：

CREATE TABLE [dbo].[tableA](
    [CustomerId] [nvarchar](max) NULL,
    [Housenumber] [nvarchar](max) NULL,
    [Visit Id] [nvarchar](max) NULL,      ---primary key
    [Zipcode] [nvarchar](max) NULL,
    [City] [nvarchar](max) NULL,
    [Visit Date] [nvarchar](max) NULL,
    [Duration] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[tableB](
        [CustomerId] [nvarchar](2000) NULL,
        [Housenumber] [nvarchar](2000) NULL,
        [Visit Id] [nvarchar](2000) NULL,      
        [Zipcode] [nvarchar](2000) NULL,
        [City] [nvarchar](2000) NULL,
        [Visit Date] [datetime] NULL,
        [Duration] [float](max) NULL,
        [Counter 1] [nvarchar](100) NULL,
        [Counter 2] [nvarchar](100) NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[error_log](
        [CustomerId] [nvarchar](max) NULL,
        [Housenumber] [nvarchar](max) NULL,
        [Visit Id] [nvarchar](max) NULL,      ---primary key
        [Zipcode] [nvarchar](max) NULL,
        [City] [nvarchar](max) NULL,
        [Visit Date] [nvarchar](max) NULL,
        [Duration] [nvarchar](max) NULL,
        [errorvalue] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

任何人都可以建议我如何比较数据类型并在存储过程的帮助下完成这项任务。

标签： sqlsql-servertsqlstored-procedures

解决方案

仅比较数据类型是不够的。
如果是/////数据还需要比较最大允许长度， 如果是nvarchar///需要比较尺度。如果是/你需要比较比例和精度。ncharvarcharcharvarbinarybinary
datetimedatetime2datetimeoffsetdecimalnumeric

要检查键约束，您需要再查询两个表 -sys.indexes和sys.index_columns.

幸运的是，您不必自己编写整个内容，因为我之前已经为我的最新博客文章完成了很多工作。

代码的相关部分在脚本的开头，我在其中填充了一个表，稍后我将使用它来构建动态 SQL - 这是 select 语句（我添加了别名以便于理解）：

SELECT  col.Name As ColumnName, 
        typ.Name As TypeName,
        CAST(col.max_length as nvarchar(11)) As MaxLength, 
        IIF(typ.name IN ('nvarchar', 'nchar', 'varchar', 'char', 'varbinary', 'binary'), 1, 0) As IncludeMaxLength, 
        IIF(idx.is_primary_key = 1, 1, 0) As IsPkColumn,
        col.is_identity As IsIdentity,
        col.is_computed As IsComputed,
        SIGN(/*col.generated_always_type*/0) As IsAutoGenerated, 
        IIF(typ.name IN('numeric', 'decimal'), 1, 0) As IncludePrecisionAndScale,
        col.[precision],
        IIF(typ.name IN('datetimeoffset', 'datetime2', 'time'), 1, 0) As IncludeScale,
        col.scale
FROM sys.columns as col
JOIN sys.types As typ
    ON  col.system_type_id = typ.system_type_id 
    AND col.user_type_id = typ.user_type_id 
JOIN sys.tables as tab
    ON col.object_id = tab.object_id 
LEFT JOIN sys.index_columns idxCol
    ON  col.object_id = idxCol.object_id 
    AND idxCol.column_id = col.column_id
LEFT JOIN sys.indexes idx
    ON  idx.object_id = tab.object_id 
    AND idxCol.index_id = idx.index_id
WHERE tab.name = @TableName

您可以在两个表上运行此查询并比较表中任何列的任何相关数据。

推荐阅读