过程仅插入75个条目中的单个值 [英] Procedure insert only single value out of 75 entries

问题描述

你好朋友....

我已经开发了程序 sp_insertbatch_1_month 但它只插入75个值中的单个值。

hello friends....
I have developed procedure sp_insertbatch_1_month but it insert only single value out of 75 values.

USE [PIMS_DB]
GO
/****** Object:  StoredProcedure [dbo].[sp_insertbatch_1_month]    Script Date: 02/16/2015 17:57:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_insertbatch_1_month]
@year varchar(4),
@month varchar(2)
as
begin
	declare @StartDt DATETIME
	declare @EndDt  DATETIME
	set @StartDt=@year+'/'+@month+'/'+'01 00:00:00'
	set @EndDt=dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(day,-1,dateadd(month,1,@StartDt)))))
	
	if (convert(varchar,@EndDt,101)>=convert(varchar,getdate(),101) and month(@EndDt)>=month(getdate()) and year(@EndDt)>=year(getdate()))
	begin
		set @EndDt=(select getdate())
	end
	declare @starttime varchar(20)
	declare @endtime varchar(20)
	set @starttime=cast((month(@StartDt)) as varchar)+'/'+cast((day(@StartDt)) as varchar)+'/'+cast((year(@StartDt)) as varchar)+' '+cast((datepart(hour,@StartDt)) as varchar)+':'+cast((datepart(minute,@StartDt)) as varchar)+':'+cast((datepart(second,@StartDt)) as varchar)
	set @endtime=cast((month(@EndDt)) as varchar)+'/'+cast((day(@EndDt)) as varchar)+'/'+cast((year(@EndDt)) as varchar)+' '+cast((datepart(hour,@EndDt)) as varchar)+':'+cast((datepart(minute,@EndDt)) as varchar)+':'+cast((datepart(second,@EndDt)) as varchar)
	declare @var1 varchar(5000)
	set @var1='select * from openquery(pims_hist,''set samplingmode=interpolated,rowcount=0 select distinct value from ihrawdata where tagname=PIMS-LOTE-1.LOTE.PIMS.Global.BATCH_1.DATA and timestamp>="'+@starttime+'" and timestamp<="'+@endtime+'" and value like LT*'')'
	
	create table #temp
	(
		id int identity(1,1) not null,
		batch varchar(20)
	)
	
	insert into #temp exec(@var1)
	
	declare @count int
	declare @next int
	
	select @count=COUNT(batch) from #temp
	declare @batch varchar(20)
	set @next=@count-1
	if (@count>0)
	begin
	
		while(@next>=0)
		begin
			select top 1 @batch=batch from (select top (@count-@next) *  from #temp order by id desc) as f order by f.id asc
			insert into Batch_1_Data select @batch
			set @next=@next-1
		end
	end
end

它应该插入以下语句返回的所有75个值

it is supposed to insert all 75 values returned by following statement

insert into #temp exec(@var1)

请帮帮我

Please help me

推荐答案

IF (ISNULL(@VAR1, 0) <> 0)
BEGIN
--TEMP TABLE
DECLARE #TEMP TABLE
(
	ID INT IDENTITY(1,1) NOT NULL,
	BATCH VARCHAR(20)
) 
--INSERT #TEMP STATEMENTS	
INSERT INTO #TEMP EXEC(@VAR1) 
--VARIABLE DECLARATION
DECLARE @COUNT INT
 
--SELECT COUNT
SELECT @COUNT=COUNT(BATCH) FROM #TEMP
DECLARE @BATCH VARCHAR(20)

--INSERT MULTIPLE RECORDS
IF(@COUNT > 0)
	BEGIN
		SET @EMPLOOPCOUNT = 1;
		WHILE (@TEMPLOOPCOUNT <= @COUNT)
		BEGIN 
			--SELECT YOUR VALES 
			SELECT @BATCH = BATCH FROM #TEMP WHERE ID = @TEMPLOOPCOUNT
			-- INSERTING VALUES INTO EMPLOYEEPROJECTS TABLE
			INSERT INTO BATCH_1_DATA
			(	
				--COLUMNS
				-- EG:ID
				BATCH
			)
			VALUES 
			(
			   --SELECTED VALUES FROM #TEMP
			   -- EG:@ID
			   @BATCH
			)
			SET  @TEMPLOOPCOUNT = @TEMPLOOPCOUNT + 1
	    END
	END
END

尝试使用此代码代替

Try this code in place of

create table #temp
	(
		id int identity(1,1) not null,
		batch varchar(20)
	)
	
	insert into #temp exec(@var1)
	
	declare @count int
	declare @next int
	
	select @count=COUNT(batch) from #temp
	declare @batch varchar(20)
	set @next=@count-1
	if (@count>0)
	begin
	
		while(@next>=0)
		begin
			select top 1 @batch=batch from (select top (@count-@next) *  from #temp order by id desc) as f order by f.id asc
			insert into Batch_1_Data select @batch
			set @next=@next-1
		end

这篇关于过程仅插入75个条目中的单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！