SQL 列名或提供的值数量与表定义不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1152932/
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
Column name or number of supplied values does not match table definition
提问by
In SQL server, I am trying to insert values from one table to another by using the below query:
在 SQL Server 中，我尝试使用以下查询将值从一个表插入到另一个表：
delete from tblTable1
insert into tblTable1 select * from tblTable1_Link
I am getting the following error:
我收到以下错误：
Column name or number of supplied values does not match table definition.
列名或提供的值数量与表定义不匹配。
I am sure that both the tables have same structure, same column names and same data types.
我确信这两个表具有相同的结构、相同的列名和相同的数据类型。
Please help!
请帮忙！
采纳答案by gbn
They don't have the same structure... I can guarantee they are different
他们没有相同的结构......我可以保证他们是不同的
I know you've already created it... There is already an object named ‘tbltable1' in the database
我知道你已经创建了它......数据库中已经有一个名为'tbltable1'的对象
What you may want is this (which also fixes your other issue):
您可能想要的是这个（这也解决了您的其他问题）：
Drop table tblTable1
select * into tblTable1 from tblTable1_Link
回答by Adriaan Stander
for inserts it is always better to specify the column names see the following
对于插入，最好指定列名，请参阅以下内容
DECLARE @Table TABLE(
Val1 VARCHAR(MAX)
)
INSERT INTO @Table SELECT '1'
works fine, changing the table def to causes the error
工作正常，将表 def 更改为导致错误
DECLARE @Table TABLE(
Val1 VARCHAR(MAX),
Val2 VARCHAR(MAX)
)
INSERT INTO @Table SELECT '1'
Msg 213, Level 16, State 1, Line 6 Insert Error: Column name or number of supplied values does not match table definition.
消息 213，级别 16，状态 1，第 6 行插入错误：列名或提供的值数量与表定义不匹配。
But changing the above to
但将上述更改为
DECLARE @Table TABLE(
Val1 VARCHAR(MAX),
Val2 VARCHAR(MAX)
)
INSERT INTO @Table (Val1) SELECT '1'
works. You need to be more specific with the columns specified
作品。您需要更具体地指定列
supply the structures and we can have a look
提供结构，我们可以看看
回答by Kris Benedict
This is an older post but I want to also mention that if you have something like
这是一篇较旧的帖子，但我还想提一下，如果你有类似的东西
insert into blah
select * from blah2
and blah and blah2 are identical keep in mind that a computed column will throw this same error...
和 blah 和 blah2 是相同的请记住，计算列会抛出同样的错误......
I just realized that when the above failed and I tried
我刚刚意识到当上述失败时我尝试了
insert into blah (cola, colb, colc)
select cola, colb, colc from blah2
In my example it was fullname field (computed from first and last, etc)
在我的示例中，它是全名字段（从第一个和最后一个等计算）
回答by CodeCoder
The problem is that you are trying to insert data into the database without using columns. Sql server gives you that error message.
问题是您试图在不使用列的情况下将数据插入数据库。Sql server 给你那个错误信息。
error: insert into users values('1', '2','3')- this works fine as long you only have 3 columns
错误：insert into users values('1', '2','3')- 只要您只有 3 列，就可以正常工作
if you have 4 columns but only want to insert into 3 of them
如果您有 4 列但只想插入其中的 3 列
correct: insert into users (firstName,lastName,city) values ('Tom', 'Jones', 'Miami')
正确的： insert into users (firstName,lastName,city) values ('Tom', 'Jones', 'Miami')
hope this helps
希望这可以帮助
回答by ScottieB
Dropping the table was not an option for me, since I'm keeping a running log. If every time I needed to insert I had to drop, the table would be meaningless.
删除桌子对我来说不是一个选择，因为我保留了一个运行日志。如果每次我需要插入时我都必须删除，那么该表将毫无意义。
My error was because I had a couple columns in the create table statement that were products of other columns, changing these fixed my problem. eg
我的错误是因为我在 create table 语句中有几列是其他列的产品，更改这些可以解决我的问题。例如
create table foo (
field1 as int
,field2 as int
,field12 as field1 + field2 )
create table copyOfFoo (
field1 as int
,field2 as int
,field12 as field1 + field2) --this is the problem, should just be 'as int'
insert into copyOfFoo
SELECT * FROM foo
回答by Kris Sam
For me the culprit is int value assigned to salary
对我来说，罪魁祸首是分配给薪水的 int 值
Insert into Employees(ID,FirstName,LastName,Gender,Salary) values(3,'Canada', 'pa', 'm',15,000)
插入到雇员(ID,FirstName,LastName,Gender,Salary) 值(3,'Canada', 'pa', 'm',15,000)
in salary column When we assign 15,000 the compiler understand 15 and 000.
在薪水列中当我们分配 15,000 时，编译器会理解 15 和 000。
This correction works fine for me. Insert into Employees(ID,FirstName,LastName,Gender,Salary) values(4,'US', 'sam', 'm',15000)
这个更正对我来说很好。插入到雇员(ID,FirstName,LastName,Gender,Salary) 值中(4,'US','sam','m',15000)
回答by Mu?ir Ur Rêhm??
Check your id. Is it Identity? If it is then make sure it is declared as ID not null Identity(1,1)
检查您的id. 是身份吗？如果是，则确保将其声明为ID not null Identity(1,1)
And before creating your table , Drop tableand then create table.
在创建表之前，Drop table然后create table。
回答by FredyWenger
Update to SQL server 2016/2017/…
We have some stored procedures in place to import and export databases.
In the sp we use (amongst other things) RESTORE FILELISTONLY FROM DISK where we create a
table "#restoretemp" for the restore from file.
更新到 SQL Server 2016/2017/...
我们有一些存储过程来导入和导出数据库。
在 sp 中，我们使用（除其他外）RESTORE FILELISTONLY FROM DISK，我们在其中创建了一个表“#restoretemp”，用于从文件中恢复。
With SQL server 2016, MS has added a field SnapshotURL nvarchar(360)(restore url Azure) what has caused the error message.
After I have enhanced the additional field, the restore has worked again.
Code snipped (see last field):
在 SQL Server 2016 中，MS 添加了一个字段SnapshotURL nvarchar(360)(restore url Azure) 导致错误消息的原因。
在我增强了附加字段后，恢复再次起作用。
代码剪断（见最后一个字段）：
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32)
-- Added field 01.10.2018 needed from SQL Server 2016 (Azure URL)
,SnapshotURL nvarchar(360)
)
INSERT #restoretemp EXEC (@query)
SET @errorstat = @@ERROR
if @errorstat <> 0
Begin
if @Rueckgabe = 0 SET @Rueckgabe = 6
End
Print @Rueckgabe
回答by linitux
Beware of triggers. Maybe the issue is with some operation in the trigger for inserted rows.
小心触发器。也许问题在于插入行的触发器中的某些操作。
回答by GyurcIT
The computed columns make the problem.
Do not use SELECT *. You must specify each fields after SELECT except computed fields
计算列会产生问题。不要使用SELECT *. 您必须在 SELECT 之后指定每个字段，计算字段除外
