如何使用2 varchar类型查询在单个临时表中插入数据 [英] how to insert data in a single temp table using 2 varchar type query

问题描述

hii all ..



我想在临时表中输入数据。使用2 varchar类型查询..



我的存储过程是：



错误是：

hii all..

i want to enter data in a temp table . using 2 varchar type query..

my stored procedure is:

the error is:

Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.

USE [scjdata]
GO
/****** Object:  StoredProcedure [dbo].[alldistt_wise]    Script Date: 05/06/2014 15:54:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[alldistt_wise] 
	
    @Sex VARCHAR (250),
    @DisabiltyType VARCHAR (250),
    @SocialGroup  VARCHAR (250),
    @Minority VARCHAR (250),
    @Age_start VARCHAR (250),
    @Age_End VARCHAR (250)
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
	@FamilyTblName AS VARCHAR (125), 
    @vQuery AS VARCHAR (max), 
    @vQuery1 as varchar(max);
      CREATE TABLE #tblagereoprt
    ( 
       disttname nvarchar(20),
       totalpopulation   nvarchar(20)  
    );
    
    DECLARE @N varchar(3)
    SET @N = '1' 
    WHILE (@N <= 27)
    BEGIN
    SET @ChildTblName = '[' + RIGHT(('0'+@N),2) + 'Child_D' + ']'
    SET @FamilyTblName = '[' + RIGHT(('0'+@N),2) + 'Family_D' + ']'
    
     
      
    SET @vQuery1 = 'select distname as disttname from DIST where distt='+@N+''
    
    SET @vQuery = 'select count(Child.cname) as totalpopulation from ' + @ChildTblName + ' As Child  INNER JOIN ' + @FamilyTblName + ' 
      AS Family  ON Child.barcode=Family.barcode where Child.distt='+@N+''; 
     
    IF(@Sex <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.sex = ''' + @Sex + ''''
    END  
    IF(@DisabiltyType <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.disability = ''' + @DisabiltyType + ''''
    END 
    IF(@Age_start <> '' and @Age_End <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND (Child.age between ''' + @Age_start + ''' and ''' + @Age_End + ''')'
    END 
    
    IF(@SocialGroup <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.social_grp = ''' + @SocialGroup + ''''
    END 
    IF(@Minority <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.minority = ''' + @Minority + ''''
    END 
    
    insert into #tblagereoprt
    exec(@vQuery1)
    exec(@vQuery)
   
    SET @N = @N +1
    END
    select * from  #tblagereoprt 
    drop table #tblagereoprt;
  
END

提前付款

thanks in advance

推荐答案

你的表

#tblagereoprt

接受两个值，但是在插入时你正在使用查询的执行语句

is accepting two values, But while inserting you are using exec statement of query

@vQuery1

这只会给你一个结果。



试试这样



首先将结果保存在两个不同的变量中并同时插入两个变量。

which will give you only one result.

try like this

first save your results in two different variables and insert both at same time.

这篇关于如何使用2 varchar类型查询在单个临时表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！