StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGet an error in SQL Server 2008 stored procedure program about not matching table definition
text
Body
copied!<p>I have written a stored procedure that get will create a temp table ... get Excel table into temp and then merge it with my table ... the problem is I got this error =></p> <blockquote> <p>Msg 213, Level 16, State 1, Procedure Insertsomething, Line 120<br> Column name or number of supplied values does not match table definition.</p> </blockquote> <p>It's not telling the truth as I have made both definition very clear right and both match with each other! Not sure what should I do anymore.</p> <p>My code is:</p> <pre><code> USE [something] GO /****** Object: StoredProcedure [dbo].[Insertsomething] Script Date: 07/08/2013 10:56:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Insertsomething] @path nvarchar(300) = '' AS IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp]') AND type in (N'U')) DROP TABLE [dbo].[temp]; create table temp( cod nvarchar(10), name_pr nvarchar(100), name_pe nvarchar(100), en bit, ending nvarchar(1), b nvarchar(10), date_p nvarchar(10), nek nvarchar(10), date_kh nvarchar(10), mp int, mt int, no_p nvarchar(20), mas nvarchar(50), mablag bigint, np bit, nf bit, nn bit, hpp1 int, hpp2 int, hpp3 int, hpp4 int, hpp5 int, hpp6 int, hpp7 int, hpp8 int, hpf1 int, hpf2 int, hpf3 int, hpf4 int, hpf5 int, hpf6 int, hpf7 int, hpf8 int, mola ntext, name1 nvarchar(50), name2 nvarchar(50), name3 nvarchar(50), name4 nvarchar(50), name5 nvarchar(50), name6 nvarchar(50), mab_t bigint, zarib1 float, zarib2 float, zarib3 float, zarib4 float, datet1 nvarchar(10), datet2 nvarchar(10), grup nvarchar(20), mablag1 bigint ); IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project]') AND type in (N'U')) begin CREATE TABLE [dbo].[project]( [cod] [nvarchar](10) NOT NULL, [name_pr] [nvarchar](100) NULL, [name_pe] [nvarchar](100) NULL, [en] [bit] NULL, [ending] [nvarchar](1) NULL, [b] [nvarchar](10) NULL, [date_p] [nvarchar](10) NULL, [nek] [nvarchar](10) NULL, [date_kh] [nvarchar](10) NULL, [mp] [int] NULL, [mt] [int] NULL, [no_p] [nvarchar](20) NULL, [mas] [nvarchar](50) NULL, [mablag] [bigint] NULL, [np] [bit] NULL, [nf] [bit] NULL, [nn] [bit] NULL, [hpp1] [int] NULL, [hpp2] [int] NULL, [hpp3] [int] NULL, [hpp4] [int] NULL, [hpp5] [int] NULL, [hpp6] [int] NULL, [hpp7] [int] NULL, [hpp8] [int] NULL, [hpf1] [int] NULL, [hpf2] [int] NULL, [hpf3] [int] NULL, [hpf4] [int] NULL, [hpf5] [int] NULL, [hpf6] [int] NULL, [hpf7] [int] NULL, [hpf8] [int] NULL, [mola] [ntext] NULL, [name1] [nvarchar](50) NULL, [name2] [nvarchar](50) NULL, [name3] [nvarchar](50) NULL, [name4] [nvarchar](50) NULL, [name5] [nvarchar](50) NULL, [name6] [nvarchar](50) NULL, [mab_t] [bigint] NULL, [zarib1] [float] NULL, [zarib2] [float] NULL, [zarib3] [float] NULL, [zarib4] [float] NULL, [datet1] [nvarchar](10) NULL, [datet2] [nvarchar](10) NULL, [grup] [nvarchar](20) NULL, [mablag1] [bigint] NULL, [cod_g] [nvarchar](10) NULL, [cod_m] [nvarchar](10) NULL, CONSTRAINT [PK_project] PRIMARY KEY CLUSTERED ( [cod] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] end; exec('INSERT INTO temp SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' , ''Excel 8.0;Database='+@path+';HDR=YES'' , ''select * from [sheet1$]'')'); merge project as tar using dbo.temp as sor on tar.cod=sor.cod when matched then UPDATE SET tar.cod = sor.cod , tar.name_pr = sor.name_pr , tar.name_pe = sor.name_pe , tar.en = sor.en , tar.ending = sor.ending , tar.b = sor.b , tar.date_p = sor.date_p , tar.nek = sor.nek , tar.date_kh = sor.date_kh , tar.mp = sor.mp , tar.mt = sor.mt , tar.no_p = sor.no_p , tar.mas = sor.mas , tar.mablag = sor.mablag , tar.np = sor.np , tar.nf = sor.nf , tar.nn = sor.nn , tar.hpp1 = sor.hpp1 , tar.hpp2 = sor.hpp2 , tar.hpp3 = sor.hpp3 , tar.hpp4 = sor.hpp4 , tar.hpp5 = sor.hpp5 , tar.hpp6 = sor.hpp6 , tar.hpp7 = sor.hpp7 , tar.hpp8 = sor.hpp8 , tar.hpf1 = sor.hpf1 , tar.hpf2 = sor.hpf2 , tar.hpf3 = sor.hpf3 , tar.hpf4 = sor.hpf4 , tar.hpf5 = sor.hpf5 , tar.hpf6 = sor.hpf6 , tar.hpf7 = sor.hpf7 , tar.hpf8 = sor.hpf8 , tar.mola = sor.mola , tar.name1 = sor.name1 , tar.name2 = sor.name2 , tar.name3 = sor.name3 , tar.name4 = sor.name4 , tar.name5 = sor.name5 , tar.name6 = sor.name6 , tar.mab_t = sor.mab_t , tar.zarib1 = sor.zarib1 , tar.zarib2 = sor.zarib2 , tar.zarib3 = sor.zarib3 , tar.zarib4 = sor.zarib4 , tar.datet1 = sor.datet1 , tar.datet2 = sor.datet2 , tar.grup = sor.grup , tar.mablag1 = sor.mablag1 WHEN NOT MATCHED then insert values (sor.cod,sor.name_pr,sor.name_pe,sor.en,sor.ending,sor.b,sor.date_p,sor.nek,sor.date_kh,sor.mp,sor.mt,sor.no_p,sor.mas,sor.mablag,sor.np,sor.nf,sor.nn,sor.hpp1,sor.hpp2,sor.hpp3,sor.hpp4,sor.hpp5,sor.hpp6,sor.hpp7,sor.hpp8,sor.hpf1,sor.hpf2,sor.hpf3,sor.hpf4,sor.hpf5,sor.hpf6,sor.hpf7,sor.hpf8,sor.mola,sor.name1,sor.name2,sor.name3,sor.name4,sor.name5,sor.name6,sor.mab_t,sor.zarib1,sor.zarib2,sor.zarib3,sor.zarib4,sor.datet1,sor.datet2,sor.grup,sor.mablag1); </code></pre> <p>Any idea?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload