Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
Hello All,
I tried to merge record in 2 different table by sending 1 datatable as parameter, but it giving me error message "
Column name or number of supplied values does not match table definition."

 
Belwo is my Stored Procedure
 
I would appreciate any help.
 
Thanks
Anand
 

ALTER PROCEDURE [dbo].[TracerWS_Merge_PickList] 
	-- Add the parameters for the stored procedure here
	@PickListDataTable TracerWS_PickListTableType READONLY
AS
BEGIN
	declare @PickListValueID int
	declare @name nvarchar(200)
	declare @Note nvarchar(2000)
	declare @NewPickLists table(PickListID bigint,OrganizationID bigint,CustomerID bigint,Value nvarchar(200),pickListTypeId int,Action varchar(30))
	declare @NewPickListKey table(ParentID bigint, ParentTypeID bigint, LegacyPickListID nvarchar(200),PickListTypeID bigint,CustomerID bigint,OrganizationID bigint,DateAdded datetime,AddedBy bigint,DateModified datetime,ModifiedBy bigint,DateDeleted datetime,DeletedBy bigint,IsActive bit) 
	declare @NewPickListnews table(Name nvarchar(2000),Value nvarchar(2000),Note nvarchar(2000))
	--MERGE @PickListDataTable with PickList Table
	--commented and added by anand dated 30th Oct 2012
	
	--Merge for PickListValue
	
	insert into @NewPickListnews(Name, Value, Note) SELECT Name, Value, Note FROM @PickListDataTable
	insert into @NewPickListKey(ParentID, ParentTypeID, LegacyPickListID,PickListTypeID,CustomerID,OrganizationID,DateAdded,AddedBy,DateModified,ModifiedBy,DateDeleted,DeletedBy,IsActive) SELECT ParentID, ParentTypeID, LegacyPickListID,PickListTypeID,CustomerID,OrganizationID,DateAdded,AddedBy,DateModified,ModifiedBy,DateDeleted,DeletedBy,IsActive FROM @PickListDataTable
	
	
	MERGE PickListValue as P
    USING @NewPickListnews as TVP
    ON P.Name=TVP.Name
    and P.Value=TVP.Value
    and P.Note=TVP.Note
    --WHEN MATCHED THEN 
    --UPDATE SET 
    --  Name = TVP.Name
    --  ,Value = TVP.Value
    --  ,Note = TVP.Note
      
    WHEN NOT MATCHED THEN 
      INSERT(Name,Value,Note)
	  VALUES( TVP.Name, TVP.Value, TVP.Note)
	OUTPUT Inserted.PickListValueID, $action into @NewPickListnews;
	
	
	
	----Merge for PickListKey
	MERGE PickListKey as P
    USING @NewPickListKey as TVP
    ON P.LegacyPickListID=TVP.LegacyPickListID
    and P.OrganizationID = TVP.OrganizationID
    and P.CustomerID = TVP.CustomerID
    WHEN MATCHED THEN 
    UPDATE SET ParentID = TVP.ParentID
      ,ParentTypeID = TVP.ParentTypeID
      ,LegacyPickListID = TVP.LegacyPickListID
      ,PickListTypeID = TVP.PickListTypeID
      ,CustomerID = TVP.CustomerID
      ,OrganizationID = TVP.OrganizationID 
      ,PickListValueID = isnull((select PickListValueID from PicklistValue where Name= TVP.Name and Value = TVP.Value and Note=TVP.Note),0)   
      ,DateAdded = TVP.DateAdded
      ,AddedBy = TVP.AddedBy
      ,DateModified = TVP.DateModified
      ,ModifiedBy = TVP.ModifiedBy
      ,DateDeleted = TVP.DateDeleted
      ,DeletedBy = TVP.DeletedBy  
      ,IsActive = TVP.IsActive
    WHEN NOT MATCHED THEN 
      INSERT( ParentID, ParentTypeID, LegacyPickListID, PickListTypeID, CustomerID, OrganizationID,PickListValueID, DateAdded, AddedBy, DateModified, ModifiedBy, DateDeleted, DeletedBy, IsActive)
	  VALUES( TVP.ParentID, TVP.ParentTypeID, TVP.LegacyPickListID, TVP.PickListTypeID, TVP.CustomerID, TVP.OrganizationID,isnull((select PickListValueID from PicklistValue where Name= TVP.Name and Value = TVP.Value and Note=TVP.Note),0) , TVP.DateAdded, TVP.AddedBy, TVP.DateModified, TVP.ModifiedBy, TVP.DateDeleted, TVP.DeletedBy, TVP.IsActive)
	OUTPUT Inserted.PickListID,inserted.OrganizationID,inserted.CustomerID,inserted.pickListTypeId,$action into @NewPickLists;
	
	
Posted 6-Nov-12 20:39pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

hi,
if you simply search or read carefullyt the error u recieve u will find answer.
 
it says that either column name in datatable is not matching the table you are created in your SP. or the number of supplied values in datatable you are passing are not matching to columns created in SP.
 
please check that first.
 
thanks,
kk
  Permalink  
Comments
Member 4531085 at 7-Nov-12 2:05am
   
Sir, Actually i have 1 main table name @PickListDataTable, after that i declare 2 temp table and on that i make insert/update on 2 different table. and i also matched the column with actual table other than defined column i have only primark key column that has identity true.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 7 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100