Microsoft Sql Server Error 213
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and msg 213 level 16 state 1 policies of this site About Us Learn more about Stack Overflow the column name or number of supplied values does not match table definition. sql server company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Insert Into
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error Msg 213: Column name or number of supplied values does not match table definition up vote 0 down vote favorite I created a table with a stored procedure and I'm trying to insert data into it, but I'm getting this error Msg 137, Level 15, State 2, Line 49 Must declare the scalar variable "@EmployeeID") Please help! Create Table Humanresource.Employee ( EmployeeID char(3) constraint pkemployeeid primary key, FirstName varchar(20) not null, LastName varchar(20) not null, Emp_Address varchar(30) not null, Title char(30) constraint ckTitle check(Title in ('Executive','Senior Executive','Management Trainee','Event Manager','Senior Event Manager')), Phone varchar(20) check(Phone like '[0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]') ) Stored procedure: create procedure Auto_EmpID @FirstName varchar(20), @LastName varchar(20), @Address varchar(30), @Title varchar(30), @Phone varchar(20) AS Declare @EmployeeID char(3) if exists (select EmployeeID From HumanResource.Employee Where EmployeeID = 'E101') BEGIN Select @EmployeeID = Max(right(EmployeeID, 2)) from HumanResource.Employee Select @EmployeeID = CASE When @EmployeeID >= 0 AND @Employeeid < 9 Then 'E0' + convert(char(3), @EmployeeID + 1) When @EmployeeID >= 9 AND @EmployeeID < 99 Then 'E' + convert(char(3), @EmployeeID + 1) END END Else set @Employeeid = 'E101' insert into HumanResource.Employee Values(@EmployeeID, @FirstName, @LastName, @Address, @Title, @Phone) Return Exec Auto_EmpID 'Bamidele','Ajose','Lagos','11-1111-111-111' exec Auto_Empid 'Kunle','Awele','Ikeja','22-3332-655-897' exec auto_empid 'John','George','Benin','33-5555-7654-443' select * from humanresource.employee insert into humanresource.employee(@EmployeeID, @FirstName, @LastName, @Address, @Title, @Phone) values(@EmployeeID = 'e101', 'Baley', 'Carles', 'Los Angeles', '23-3445-434-344') sql-server stored-procedures share|improve this question edited Apr 29 '14 at 4:49 marc_s 454k938701032 asked Apr 29 '14 at 2:48 user3380956 add a comment| 1
Messages 2001-3000 Messages 3001-4000 Messages 4001-5000 Messages 5001-6000 Messages 6001-7000 Messages 7001-7500 Messages 7501-8000 Messages 8001-8500 Messages 8501-9000 Messages 9001-9500 Messages 9501-10000 Messages 10001-10500 Messages 10501-11000 Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 Messages 14001-14500 Home>SQL Server Error Messages> Msg 213 - Insert Error: Column name or number of supplied values does not match table definition. SQL Server Error Messages - Msg 213 - Insert Error: Column name or number of supplied values does not match table definition. SQL Server Error Messages - Msg 213 Error Message Server: Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied http://stackoverflow.com/questions/23354585/error-msg-213-column-name-or-number-of-supplied-values-does-not-match-table-def values does not match table definition. Causes: This error occurs when doing an INSERT where the columns list is not specified and the values being inserted, either through the VALUES clause or through a SELECT subquery, are either more than or less than thecolumns in the table. Here are examples on when the error can occur: -- Sample #1: Using INSERT INTO ... VALUES CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100)) http://www.sql-server-helper.com/error-messages/msg-213.aspx INSERT INTO [dbo].[Customers] VALUES (1, 'John', 'Doe') -- Sample #2: Using INSERT INTO ... SELECT FROM CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100)) INSERT INTO [dbo].[Client] SELECT [ID], [Name], [Address] FROM [dbo].[NewClient] Solution / Work Around: To avoid this problem, make sure that the values specified in the VALUES clause or in the SELECT subquery match the number of columns in the INSERT clause. In addition to this,you mustspecify the columns in the INSERT INTO clause. Although the column list in the INSERT INTO statement is optional, it is recommended that it is always specified so that even if there are any modifications made on the table, either new columns are added or inserted in the middle of the table or columns are deleted, the INSERT statement will not generate this error. (Of course, a different error message will be generated if a column is deleted from the table that is being referenced by the INSERT statement). Given the samples above, here's how to avoid the error: -- Sample #1: Using INSERT INTO ... VALUES CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100)) INSERT INTO [dbo].[Customers] ( [ID], [Name] ) VALUES (1, 'John Doe') -- Sample #2: Using INSERT INTO ... SELECT FROM CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150510 on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2005 Forums Transact-SQL http://www.sqlservercentral.com/Forums/Topic873046-149-1.aspx (2005) import returns Msg 213, Level 16, State 1 BUT Reply to Topic Printer Friendly Author Topic jar21 Yak Posting Veteran USA 51 Posts Posted-09/22/2010: 15:41:20 multiple other lines with equivalent info are sql server working?after some reading it appears that I was trying to put more information into tables I didnt create? however each line out of the entire query has the same info (I created in excel/notepad)Msg 213, Level 16, State 1, Line 19Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 29Insert Error: Column name or number of supplied values does not match table microsoft sql server definition.Msg 213, Level 16, State 1, Line 30Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 68Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 139Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 166Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 175Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 285Insert Error: Column name or number of supplied values does not match table definition.Msg 213, Level 16, State 1, Line 314Insert Error: Column name or number of supplied values does not match table definition.temporarily I tried deleting those couple of lines and then the errors would come up near where the old one was. any immediate thoughts? im fairly sure the code is accurateI did a quick search and found http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112998 however unlike that person I dont understand, and I think our issues were different despite similar errors Edited by - jar21 on 09/22/2010 15:42:06 tkizer Almighty SQL Goddess USA 38200 Posts Posted-09/22/2010: 16:20:36 Pleas
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » SQL Server 2005 General Discussion » Insert Error: Column name or number of... 18 posts,Page 1 of 212»» Insert Error: Column name or number of supplied values does not match table definition. Rate Topic Display Mode Topic Options Author Message KKV143KKV143 Posted Thursday, February 25, 2010 3:14 PM Grasshopper Group: General Forum Members Last Login: Monday, June 7, 2010 10:05 PM Points: 11, Visits: 24 We are migrating from sqlserver 2000 to sql server 2005 and Now i am stcuk with this strange thing which was supported in SQL 2000 but not in SQL 2005.Though the fix for this problem is prity simple but we are having thousands of SPs and we cannot check and fix this in each SP individually.The problem is CREATE TABLE#Table1( Column1 int, Column2 varchar(255), Column3 int NULL, Column4 int NULL )INSERT INTO #Table1SELECTTable2.Column1,Table2.Column2,Table2.Column3From Table2I know in the above example I am not providing the value for column4 but this statement runs fine in SQL 2000 and it inserts Null to the column4 but in SQL 2005 it generates the Insert Error: "Column name or number of supplied values does not match table definition"I tried the above statement in SQL 2005 with compatibility 70 and 80 also but it didn't work.Is there any Server setting in SQL 2005 which can allow this type of staements? Post #873046 Toby WhiteToby White Posted Friday, February 26, 2010 11:38 AM SSC-Addicted Group: General Forum Members Last Login: Tuesday, March 3, 2015 4:07 PM Points: 493, Visits: 639 I get the same error in SQL 2000 as I expected. Is it possible that you are assuming incorrectly that the procedure ran successfully in SQL Server 2000? I am willing to bet that it did not.Regards,Toby Post #873626 Sean LangeSean Lange Posted Friday, February 26, 2010 12:28 PM SSCoach Group: General Forum Members Last Login: Today @ 8:57 AM Points: 16,061, Visits: 16,673 If you know that you want null in column 4 just change your insert.INSERT INTO #Table1SELECTTable2.Column1,Table2.Column2,Table2.Column3,nullFrom Table2 _______________________________________________________________Need help? Hel