SQL 插入时出现 TSQL 错误“字符串或二进制数据将被截断”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24372512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL error on insert "String or binary data would be truncated"
提问by intA
In the code below I am inserting values into a table and getting the error "String or binary data would be truncated."
在下面的代码中，我将值插入表中并收到错误“字符串或二进制数据将被截断”。
My table definition:
我的表定义：
CREATE TABLE urs_prem_feed_out_control
(
bd_pr_cntl_rec_type char(7) NULL ,
pd_pr_cntl_acctg_dte char(6) NULL ,
bd_pr_cntl_run_dte char(10) NULL ,
bd_pr_cntl_start_dte char(10) NULL ,
bd_pr_cntl_end_dte char(10) NULL ,
bd_pr_cntl_rec_count char(16) NULL ,
bd_pr_tot_premium char(16) NULL ,
bd_pr_tot_commission char(16) NULL ,
fd_ctl_nbr integer NOT NULL
)
DECLARE @cur_fd_ctl_nbr INT = 2,
@acctg_cyc_ym_2 CHAR(6) = '201402',
@rundate CHAR (10) = CONVERT(CHAR(10),GETDATE(),101),
@cycle_start_dt DATETIME = '2014-02-17',
@cycle_end_dt DATETIME = '2014-02-24',
@record_count INT = 24704,
@tot_pr_premium DECIMAL(18,2) = 476922242,
@tot_pr_comm DECIMAL(18,2) = 2624209257
Insert code (I've declared the variables as constant values for testing, I took these values from what they were at runtime):
插入代码（我已经将变量声明为用于测试的常量值，我从它们在运行时的值中获取了这些值）：
INSERT INTO urs_prem_feed_out_control
SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,
bd_pr_cntl_rec_type = 'CONTROL',
bd_pr_cntl_acctg_dte = @acctg_cyc_ym_2,
bd_pr_cntl_run_dte = @rundate,
bd_pr_cntl_start_dte = CONVERT(CHAR(10),@cycle_start_dt,101),
bd_pr_cntl_end_dte = CONVERT(CHAR(10),@cycle_end_dt,101),
bd_pr_cntl_rec_count = RIGHT('0000000000000000' + RTRIM(CONVERT(CHAR(16),@record_count)),16),
bd_pr_tot_premium = CASE
WHEN @tot_pr_premium < 0
THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
ELSE
'+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
END,
bd_pr_tot_commission = CASE
WHEN @tot_pr_comm < 0
THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
ELSE
'+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
END
When I look at each value individually it seems like they are all within the variable length constraints of the table. Any idea why I'm getting this error?
当我单独查看每个值时，它们似乎都在表的可变长度约束范围内。知道为什么我会收到此错误吗？
Thanks!
谢谢！
采纳答案by Satwik Nadkarny
The problem with your insert query is THE ORDER OF INSERTION:
您的插入查询的问题是INSERTION 的顺序：
SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,
This column must be defined at the last in the INSERTas its the last column defined in the create table script.
此列必须INSERT在创建表脚本中定义的最后一列中定义在最后。
Change your query to this:
将您的查询更改为：
INSERT INTO #urs_prem_feed_out_control (fd_ctl_nbr, bd_pr_cntl_rec_type, pd_pr_cntl_acctg_dte, bd_pr_cntl_run_dte, bd_pr_cntl_start_dte, bd_pr_cntl_end_dte, bd_pr_cntl_rec_count, bd_pr_tot_premium, bd_pr_tot_commission)
SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,
bd_pr_cntl_rec_type = 'CONTROL',
bd_pr_cntl_acctg_dte = @acctg_cyc_ym_2,
bd_pr_cntl_run_dte = @rundate,
bd_pr_cntl_start_dte = CONVERT(CHAR(10),@cycle_start_dt,101),
bd_pr_cntl_end_dte = CONVERT(CHAR(10),@cycle_end_dt,101),
bd_pr_cntl_rec_count = RIGHT('0000000000000000' + RTRIM(CONVERT(CHAR(16),@record_count)),16),
bd_pr_tot_premium = CASE
WHEN @tot_pr_premium < 0
THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
ELSE
'+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
END,
bd_pr_tot_commission = CASE
WHEN @tot_pr_comm < 0
THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
ELSE
'+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
END
Doing this would also work. Notice that the first column here in the SELECTwithin the INSERTis just the way you have provided in your question.
这样做也会奏效。请注意，在这里的第一列SELECT中的INSERT仅仅是你已经在你的问题中提供的方法。
See this here-> http://sqlfiddle.com/#!3/0e09b/1
在这里看到这个-> http://sqlfiddle.com/#!3/0e09b/1
Hope this helps!!!
希望这可以帮助！！！
回答by HLGEM
This is why you should never write an insert statement without specifying the columns. Since you did not, it wil try to put the data in the columns in the order they are in the table which is not at all the order you have them in.
这就是为什么你不应该在没有指定列的情况下编写插入语句。由于您没有，它会尝试将数据按它们在表中的顺序放在列中，这根本不是您拥有它们的顺序。
Another thing that can happen when you get this sort of message (but which I don't think applies in your case, I include it for people searching later) is that the eeror is actually coming from a trigger and not the main insert.
当您收到此类消息时可能发生的另一件事（但我认为这不适用于您的情况，我将其包含在稍后搜索的人员中）是 eeror 实际上来自触发器而不是主要插入。
Finally a note on database design, you should not be using char for dates, you should be using date fields. You cannot do date math on a char field and it will accept incorrect date values like feb30, 2014. It is always a bad idea to store dates as anything except date or datetime values. In general char should only be used rarely when a column will always have the same number of characters (like a 2 column state abbreviation), it should not be used as the default datatype. You need to do a better job of defining datatypes that match the type and size of data being stored. You can run into problems with queries as 'VA' is not the same thing as 'VA '. In general my experience is that less than 1 % of all database fields should be Char.
最后一个关于数据库设计的说明，你不应该使用 char 作为日期，你应该使用日期字段。您不能对 char 字段进行日期数学运算，它会接受不正确的日期值，例如 2014 年 2 月 30 日。将日期存储为除日期或日期时间值以外的任何内容总是一个坏主意。一般来说，当一列总是具有相同数量的字符（如 2 列状态缩写）时，应该很少使用 char，它不应该用作默认数据类型。您需要更好地定义与所存储数据的类型和大小相匹配的数据类型。您可能会遇到查询问题，因为“VA”与“VA”不同。一般来说，我的经验是所有数据库字段中应该少于 1% 是 Char。
回答by Rawle
I think it may be beneficial to double check your work. Consider the following example code:
我认为仔细检查你的工作可能是有益的。考虑以下示例代码：
DECLARE @Table TABLE (Name NVARCHAR(1))
INSERT INTO @Table (Name) SELECT 'ab'
INSERT INTO @Table (Name) SELECT SUBSTRING('ab',1,2)
INSERT INTO @Table (Name) SELECT RIGHT('abc',2)
All yield the following: String or binary data would be truncated.
所有结果如下：字符串或二进制数据将被截断。
