[DT_UAMT],[DT_DAMT],[DT_AMT] INTO Temp_DT_PODT FROM ( NULL AS DT_UAMT ,convert(varchar,convert(decimal(18,6),isnull(usd_amount,‘0‘) * 100)) as DT_DAMT ,convert(varchar,convert(decimal(18,6),isnull(LINE_AMOUNT,‘0‘) * 100)) as DT_AMT FROM OracleDB.[dbo].OracleTB
)

 

当然了 字段不止这三个，一个表有将近100个字段，这里只是举例说明问题。

同事这里用到了SELECT INTO语句。

SQL SELECT INTO 语句可用于创建表的备份复件。

这样写可以快速达到效果不用一个一个表去建并且保证数据可以完全导出过来。

但是这样写会有问题，

<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">、首先SELECT INTO需要into的这个表不存在，所以每次都需要把Temp_DT_PODT给Drop掉。
</span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">、由于Oracle的字段类型和我们的不一样，会导致创建出来的字段类型和我们原数据库的类型不一样。
</span><span style="color: #ff0000;"><span style="font-weight: bold;">3</span>、当用 null as成一个字段的时候，into的表中对应字段类型为int</span>

上述第三条会导致有些之前SP会报错....

你可能会说不用NULL as DT_UAMT用 ‘‘ as DT_UAMT。当然是可以的。

我也说过 这个SP是为了之前的系统能够跑起来写的，有些问题存在是正常的.....

 

然后解决这些问题就交给了我.....（我好苦逼,哭晕在厕所....）

毕竟SELECT INTO 语句可用于创建表的备份复件的，我们刷数据还是用insert into的好。

insert into 是需要表的。一个表将近100个字段...一共10几个表.....手动建肯定不可能了

我是这样想的。

之前SP中into的表只是字段数据类型不对，名称都是对的。我只需要把原来的表的字段类型修改对了就行了。

恩，忽然感觉工作量小了很多有没有，哈哈。

我通过数据库 任务->执行脚本  把原SP into的表的结构导出来。

再把原系统用的表结构导出来。

对比两个文件把相应的字段类型改一下。

并记录下修改的字段，写出相应的SQL 语句进行修改。

我在测试数据库中 建了一个测试数据库test

把SP into的表通过导出文件建好。

让执行我整理的SQL 语句。

哈哈，我的工作是不是做完了？

明显没有....在我修改原来的SP 将select into 改成insert into 并执行的时候.....

竟然报错了....

SQL Server报告出错：“将数据类型 <span style="color: #0000ff;">varchar</span> 转换为 numeric 时出错。”

我靠，这是什么情况？

我改了很多decimal的...难道我一个一个去试试....

我还真一个一个去试了试。结果发现时DT_DAMT这个字段出错了。

难道是数据问题？数据中有不是数字的数据？

 

<span style="color: #0000ff;">select</span> DT_DAMT <span style="color: #0000ff;">from  </span>OracleDB.[dbo].OracleTB  <span style="color: #0000ff;">where</span> <span style="color: #ff00ff;">isnumeric</span>(DT_DAMT )<span style="color: #808080;">!=</span><span style="color: #800000; font-weight: bold;">1</span>

结果返回为空....

我去，都是数字啊。

这是什么情况？

难道长度超出了？

应该不会啊，我建的表中DT_DAMT字段是 decimal（18,6）类型。

并且同事写的SP中也是转化成了 decimal（18,6）

<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>,<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">decimal</span>(<span style="color: #800000; font-weight: bold;">18</span>,<span style="color: #800000; font-weight: bold;">6</span>),<span style="color: #ff00ff;">isnull</span>(usd_amount,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">100</span>)) <span style="color: #0000ff;">as</span> DT_DAMT

(额。。。貌似还乘以了100.当时没注意到)

 

我看了一下原SP into表的DT_DAMT类型decimal（18,2）

 我靠 decimal（18,2）可以，我的decimal（18,6）就不行？

不过也没影响吧，都是decimal（18,*）的类型，小数点前面应该都是18位。

我之前真的是这么以为的。

于是我在执行了一下sql语句。

<span style="color: #0000ff;">Select</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">decimal</span>(<span style="color: #800000; font-weight: bold;">18</span>,<span style="color: #800000; font-weight: bold;">2</span>),<span style="color: #ff00ff;">isnull</span>(LINE_AMOUNT,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>) ) <span style="color: #0000ff;">as</span><span style="color: #000000;"> DT_DAMT
</span><span style="color: #0000ff;">FROM</span> OracleDB.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.OracleTB 

没报错....

<span style="color: #0000ff;">Select</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">decimal</span>(<span style="color: #800000; font-weight: bold;">18</span>,<span style="color: #800000; font-weight: bold;">6</span>),<span style="color: #ff00ff;">isnull</span>(LINE_AMOUNT,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>) ) <span style="color: #0000ff;">as</span><span style="color: #000000;"> DT_DAMT
</span><span style="color: #0000ff;">FROM</span> OracleDB.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.OracleTB 

竟然报错了

SQL Server报告出错：“将数据类型 <span style="color: #0000ff;">varchar</span> 转换为 numeric 时出错。”

于是我果断百度一下 Decimal 

 

 

Decimal 数据类型 介绍

 

Decimal为SQL Server、MySql等数据库的一种数据类型，不属于浮点数类型，可以在定义时划定整数部份以及小数部分的位数。使用精确小数类型不仅能够保证数据计算更为精确，还可以节省储存空间，例如百分比使用decimal(<span style="color: #800080;">4</span>,<span style="color: #800080;">2</span>)即可。存储数据范围是：-<span style="color: #800080;">10</span>^<span style="color: #800080;">38</span>~<span style="color: #800080;">10</span>^<span style="color: #800080;">38</span>-<span style="color: #800080;">1</span> 的固定精度和小数位的数字。一个decimal类型的数据占用了2~<span style="color: #000000;">17个字节。
</span><span style="color: #0000ff;">decimal</span><span style="color: #000000;">[ (p[ , s] )]
p (有效位数)
可储存的最大十进位数总数，小数点左右两侧都包括在内。有效位数必须是 </span><span style="color: #800080;">1</span> 至最大有效位数 <span style="color: #800080;">38</span> 之间的值。预设有效位数是 <span style="color: #800080;">18</span><span style="color: #000000;">。
s (小数位数)
小数点右侧所能储存的最大十进位数。小数位数必须是从 </span><span style="color: #800080;">0</span> 到 p 的值。只有在指定了有效位数时，才能指定小数位数。预设小数位数是 <span style="color: #800080;">0</span>；因此，<span style="color: #800080;">0</span> <= s <=<span style="color: #000000;"> p。最大储存体大小会随著有效位数而不同。
Decimal(p,s)表示数值中共有n位数，其中整数p</span>-s位，小数s位。例：<span style="color: #0000ff;">decimal</span>(<span style="color: #800080;">10</span>,<span style="color: #800080;">6</span>)，数值中共有10位数，其中整数占4位，小数占6位。

<span style="color: #ff0000;">decimal(10,6)，数值中共有10位数，其中整数占4位，小数占6位。</span>

ISNUMERIC 介绍

<span style="color: #000000;">语法
</span><span style="color: #ff00ff;">ISNUMERIC</span><span style="color: #000000;"> ( expression )
参数  expression  要计算的表达式。
返回类型  </span><span style="color: #0000ff;">int</span><span style="color: #000000;">
注释
当输入表达式得数为一个有效的整数、浮点数、</span><span style="color: #0000ff;">money</span> 或 <span style="color: #0000ff;">decimal</span> 类型，那么 <span style="color: #ff00ff;">ISNUMERIC</span> 返回 <span style="color: #800000; font-weight: bold;">1</span>；否则返回 <span style="color: #800000; font-weight: bold;">0</span>。返回值为 <span style="color: #800000; font-weight: bold;">1</span> 确保可以将 expression 转换为上述数字类型中的一种。

 

因此记录一下， 以便以后犯同样的错误。  

SQL Server 字段类型 decimal(18,6)小数点前是几位？记一次数据库SP的BUG处理

标签：