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 =>
Msg 213, Level 16, State 1, Procedure Insertsomething, Line 120
Column name or number of supplied values does not match table definition.
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.
My code is:
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);
Any idea?
