如何以块方式在临时表中捕获数据 [英] how to fatch data in temp table blockwise

问题描述

hii all。



i面临我的sql server查询问题..



my问题是我在一个特定的distt的所有块中填充单个块的数据..我想要块状地获取数据..每个disst都有不同的coulms ..

请给我正确的语法..我想使用loop..suppose no将数据存储在临时表中。特别是distt的块是6 ..那么如何在临时表块中明智地插入数据





提前感谢





我的存储过程是

hii all.

i am facing a problem with my sql server query..

my problem is that i m fatching the data of a single block in all the blocks of a particular distt.. i want to fatch data blockwise.. each disst has diffrnt differnt coulms..
please give me correct syntax.. i want to store data in temp table using loop..suppose no. of block in particular distt is 6.. then how to insert data in temp table block wise


thanks in advance


my stored procedure is

 USE [SCJ]
GO
/****** Object:  StoredProcedure [dbo].[spAGEwisechldREPORT]    Script Date: 04/07/2014 10:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAGEwisechldREPORT] 
	-- Add the parameters for the stored procedure here
	 @DISTID VARCHAR(250)
   -- @BLOCKID VARCHAR(50)
    
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
   
     @vQuery1 AS VARCHAR (max); 
    
    SET @ChildTblName = '[' + @DISTID + 'Child_D' + ']'
    --SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'  
    
    
    
      CREATE TABLE #tblagereoprt
    (
        
        blockname  nvarchar(30),
        stage1   nvarchar(20), 
        stage2   nvarchar(10) ,
        stage3   nvarchar(10), 
        total    nvarchar(10)
     
    );
    
  SET @vQuery1='SELECT blockname, stage1, stage2, stage3,(stage1+stage2+stage3) as total
   FROM  (SELECT blockname as blockname FROM BLOCK where distt='+ @DISTID +' ) BLK
   CROSS JOIN (SELECT COUNT(age) as stage1 FROM ' + @ChildTblName + ' where (age>=6 and age<=10)  ) a
   CROSS JOIN (SELECT COUNT(age) as stage2 FROM ' + @ChildTblName + ' where (age>=11 and age<=14)  ) b
   CROSS JOIN (SELECT COUNT(age) as stage3 FROM ' + @ChildTblName + ' where(age>=6 and age<=14)) c'
  
   
    exec(@vQuery1)
   
    select * from  #tblagereoprt
    
    drop table #tblagereoprt;
  
END

  
END

推荐答案

你想要这样的输出吗？

Do you want output like this?

ALTER PROCEDURE [dbo].[spAGEwisechldREPORT]
     @DISTID varchar(250)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ChildTblName AS VARCHAR (125)
 DECLARE @selectQuery AS VARCHAR (max)
   SET @ChildTblName = @DISTID + 'Child_D'

    SET @selectQuery = 'SELECT COUNT(age) AS Count from '+QuoteName(@ChildTblName)+' where (age>=6 and age<=10)'
    SET @selectQuery = @selectQuery + ' UNION ALL  select COUNT(age) AS Count from '+ QuoteName(@ChildTblName)+' where ISDATE(dbo)=1 and (age>=11 and age<=14)'
    SET @selectQuery = @selectQuery + ' UNION ALL SELECT COUNT(age) AS Count from '+QuoteName(@ChildTblName)+' where ISDATE(dbo)=1 and (age>=6 and age<=14)'

    EXEC (@selectQuery)
END
Go

这篇关于如何以块方式在临时表中捕获数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！