SQL Server-仅在Try_cast成功(如果不做的话)时插入

Question

我正在尝试使用具有CAST()的选择插入：

INSERT INTO table1 
  (table1_Numeric_Value)
SELECT
  TRY_CAST(e.text_found AS float) 
FROM
  Event AS e

问题是如果TRY_CAST()失败，那么整个插入物都停止了。有什么方法只有在TRY_CAST()成功的情况下才能插入？

Answer 1

只是在WHERE子句中放置条件：

INSERT INTO table1 
  (table1_Numeric_Value)
SELECT
  TRY_CAST(e.text_found AS float) 
FROM
  Event AS e
WHERE
  TRY_CAST(e.text_found AS float) IS NOT NULL

（服务器应该足够聪明，只能执行一次转换）

---

仅出于完整性，如果您想要 失败的转换以防止 all 行被插入：

declare @t table (
    text_value varchar(30)
)
declare @u table (
    val float
)
insert into @t(text_value) values ('1.0'),('def'),('2.0')
insert into @u(val)
select Val from (
select
    TRY_CAST(text_value as float) as Val,
    COUNT(*) OVER() as Cnt1,
    COUNT(TRY_CAST(text_value as float)) OVER() as Cnt2
from @t
) t
where Cnt1 = Cnt2

Answer 2

要么使用某个条件过滤所有空值。

INSERT INTO table1 
  (table1_Numeric_Value)
SELECT
  TRY_CAST(e.text_found AS float) AS table1_Numeric_Value
FROM
  Event AS e
WHERE
  TRY_CAST(e.text_found AS float) IS NOT NULL

或者您可以使用isnull或cocece选择的任何值转换为null值：

INSERT INTO table1 
  (table1_Numeric_Value)
SELECT
  COALESCE(TRY_CAST(e.text_found AS float), 0) AS table1_Numeric_Value
FROM
  Event AS e

上述两个查询的结果可能不同，根据您的要求选择。

Answer 3

您可以使用ISNUMERIC功能并过滤出任何空值。我想您的数据中有字符串字符。在插入之前检查此查询，这将确保将选择string字符的行

SELECT
  TRY_CAST(e.text_found AS float) 
FROM
  Event AS e where ISNUMERIC(e.text_found) = 1--- Selects rows with numeric 
                                                               values
  and e.text_found is not null