如何检查INSERT中哪些列的数据类型不正确？
如何检查INSERT中哪些列的数据类型不正确？
提问于 2018-04-15 19:47:28
假设我在一条INSERT语句中有200列，并且偶尔会收到其中一列的"Cannot convert“错误。问题是，我不知道哪个列导致了这个错误。
在T-SQL或mybatis中有没有办法检查哪一列的格式不正确？(我只有日期、字符、数字)。我可以对每一列使用ISNUMERIC、ISDATE，但这并不是很优雅。
我在Java中使用的是mybatis，所以我不能使用任何PreparedStatement。
回答 2
Stack Overflow用户
发布于 2018-04-16 02:46:09
您可以构建一个尝试转换每个可疑列的查询。
并将查询限制到其中一个转换尝试失败的地方。
当尝试将坏数据转换或转换为datetime或number类型时，大多数情况下，坏数据将在CHAR或VARCHAR中。
所以你可以把你的研究局限在这些方面。
此外，从错误中，您应该可以看到哪个值未能转换为哪种类型。这也有助于限制你研究的领域。
使用表变量的简化示例：
declare @T1 table (id int identity(1,1) primary key, field1 varchar(30), field2 varchar(30), field3 varchar(30));
declare @T2 table (id int identity(1,1) primary key, field1_int int, field2_date date, field3_dec decimal(10,2));
insert into @T1 (field1, field2, field3) values
('1','2018-01-01','1.23'),
('not an int','2018-01-01','1.23'),
('1','not a date','1.23'),
('1','2018-01-01','not a decimal'),
(null,'2018-01-01','1.23'),
('1',null,'1.23'),
('1','2018-01-01',null)
;
select top 1000
id,
case when try_convert(int, field1) is null then field1 end as field1,
case when try_convert(date, field2) is null then field2 end as field2,
case when try_convert(decimal(10,4), field3) is null then field3 end as field3
from @T1
where
try_convert(int, coalesce(field1, '0')) is null
or try_convert(date, coalesce(field2, '1900-01-01')) is null
or try_convert(decimal(10,4), coalesce(field3, '0.0')) is null;返回：
id field1 field2 field3
-- ---------- ----------- -------------
2 not an int NULL NULL
3 NULL not a date NULL
4 NULL NULL not a decimal如果原始数据不需要太多坏数据，您可以先尝试修复原始数据。
或者对包含错误数据的有问题的列使用try_convert。
例如：
insert into @T2 (field1_int, field2_date, field3_dec)
select
try_convert(int, field1),
try_convert(date, field2),
try_convert(decimal(10,4), field3)
from @T1; Stack Overflow用户
发布于 2018-04-16 16:43:24
对于较大的导入-特别是当您预计会出现问题时-强烈建议使用两步法。
将数据导入到一个容错能力很强的临时表中(所有NVARCHAR(MAX))
- check，都会评估、操作、更正所需的数据，并从此处执行真正的
这是一种通用的方法，您可以根据自己的需要进行调整。它将根据类型映射表检查所有表值并输出所有值，这些值在TRY_CAST中失败(需要SQL-Server 2012+)
一个模拟临时表的表(部分借用自LukStorms的answer - thx!)
CREATE TABLE #T1 (id INT IDENTITY(1,1) PRIMARY KEY
,fldInt VARCHAR(30)
,fldDate VARCHAR(30)
,fldDecimal VARCHAR(30));
GO
INSERT INTO #T1 (fldInt, fldDate, fldDecimal) values
('1','2018-01-01','1.23'),
('blah','2018-01-01','1.23'),
('1','blah','1.23'),
('1','2018-01-01','blah'),
(null,'2018-01-01','1.23'),
('1',null,'1.23'),
('1','2018-01-01',null);--类型映射(可能自动从现有目标表的INFORMATION_SCHEMA中获取)
DECLARE @type_map TABLE(ColumnName VARCHAR(100),ColumnType VARCHAR(100));
INSERT INTO @type_map VALUES('fldInt','int')
,('fldDate','date')
,('fldDecimal','decimal(10,2)');--临时表的名称
DECLARE @TableName NVARCHAR(100)='#T1'; --为每列动态创建的语句
DECLARE @columnSelect NVARCHAR(MAX)=
(SELECT
' UNION ALL SELECT id ,''' + tm.ColumnName + ''',''' + tm.ColumnType + ''',' + QUOTENAME(tm.ColumnName)
+ ',CASE WHEN TRY_CAST(' + QUOTENAME(tm.ColumnName) + ' AS ' + tm.ColumnType + ') IS NULL THEN 0 ELSE 1 END ' +
'FROM ' + QUOTENAME(@TableName)
FROM @type_map AS tm
FOR XML PATH('')
);-The最终动态创建的语句
DECLARE @cmd NVARCHAR(MAX)=
'SELECT tbl.*
FROM
(
SELECT 0 AS id,'''' AS ColumnName,'''' AS ColumnType,'''' AS ColumnValue,0 AS IsValid WHERE 1=0 '
+ @columnSelect +
') AS tbl
WHERE tbl.IsValid = 0;'--使用EXEC()执行
EXEC(@cmd);结果是：
+----+------------+---------------+-------------+---------+
| id | ColumnName | ColumnType | ColumnValue | IsValid |
+----+------------+---------------+-------------+---------+
| 2 | fldInt | int | blah | 0 |
+----+------------+---------------+-------------+---------+
| 5 | fldInt | int | NULL | 0 |
+----+------------+---------------+-------------+---------+
| 3 | fldDate | date | blah | 0 |
+----+------------+---------------+-------------+---------+
| 6 | fldDate | date | NULL | 0 |
+----+------------+---------------+-------------+---------+
| 4 | fldDecimal | decimal(10,2) | blah | 0 |
+----+------------+---------------+-------------+---------+
| 7 | fldDecimal | decimal(10,2) | NULL | 0 |
+----+------------+---------------+-------------+---------+创建的语句如下所示：
SELECT tbl.*
FROM
(
SELECT 0 AS id,'' AS ColumnName,'' AS ColumnType,'' AS ColumnValue,0 AS IsValid WHERE 1=0
UNION ALL SELECT id
,'fldInt'
,'int'
,[fldInt]
,CASE WHEN TRY_CAST([fldInt] AS int) IS NULL THEN 0 ELSE 1 END
FROM [#T1]
UNION ALL SELECT id
,'fldDate'
,'date',[fldDate]
,CASE WHEN TRY_CAST([fldDate] AS date) IS NULL THEN 0 ELSE 1 END
FROM [#T1]
UNION ALL SELECT id
,'fldDecimal'
,'decimal(10,2)'
,[fldDecimal]
,CASE WHEN TRY_CAST([fldDecimal] AS decimal(10,2)) IS NULL THEN 0 ELSE 1 END
FROM [#T1]
) AS tbl
WHERE tbl.IsValid = 0;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49841546
复制相关文章
相似问题
腾讯云开发者
