如何插入表..？ [英] how to insert into the table..?

问题描述

当我执行下面的查询时...有些错误正在抛出..

when i executing below query..some error is throwing..

insert into Tbl_leave_setting

select top 2 ROW_NUMBER() over(order by t.empcode),convert(datetime,t.dates),
t.empcode,t.types,t.anual,t.anual from
(
SELECT GETDATE() dates,[emp code] empcode,2 types,[annual] anual FROM MEGLEAVE
union all
SELECT getdate(),[emp code] ,3,[sick] FROM MEGLEAVE
)t
where t.anual is not null
order by t.empcode

消息232，16级，状态2，行1

算术溢出类型varchar的错误，值= 81002170.000000。

语句已被终止。

Msg 232, Level 16, State 2, Line 1
Arithmetic overflow error for type varchar, value = 81002170.000000.
The statement has been terminated.

推荐答案

在不知道表的情况下很难帮助你结构或看到一些样本数据。但是，这里有几个步骤可以找出问题所在。



1.突出显示

It is difficult to help you without knowing the table structures or seeing some sample data. However here are a couple of steps for you to find out where the problem is.

1. Highlight

SELECT GETDATE() dates,[emp code] empcode,2 types,[annual] anual FROM MEGLEAVE

并运行（F5）该部分 - 是否发生错误？



2.同样如此

and run (F5) just that section - does the error occur?

2. Do the same with

SELECT getdate(),[emp code] ,3,[sick] FROM MEGLEAVE

- 是否发生错误？



3.错误最有可能是因为Tbl_leave_setting中的一列声明为Varchar（x），其中x小于15



4.看看 convert（datetime，t.dates） - 列 t.dates 的类型是什么？ - 如果您要存储日期，则它应该是日期类型或至少 DateTime ，在这种情况下不需要转换。



始终使用最适合您要存储的数据的类型，不要使用Varchar（或Nvarchar，char或nchar） ）除了字母数字数据之外的任何其他内容。

- does the error occur?

3. The error is most likely to be caused because one of the columns in Tbl_leave_setting is declared as Varchar(x) where x is less than 15

4. Have a look at convert(datetime,t.dates) - what type is column t.dates? - if you are trying to store a Date then it should be of type Date or at least DateTime in which case there is no need for the Convert.

Always use the most appropriate Type for the data you are trying to store, don't use Varchar (or Nvarchar, char or nchar) for anything other than alphanumeric data.

这篇关于如何插入表..？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！