January 6, 2009 at 8:15 am
Hi all,
I am having problem at the insert part(in bold) I get insert but not all record only first row is being inserted rather than thousands!! Why? What is the problem with insert and update part?
Please help!!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[testproc]
@srcFilename varchar(256)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- create an instance of the excel app
declare @xlApp integer, @rs integer, @xlWorkBooks as integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT
-- turn off screen updates and prompts since we are remoting excel
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'
-- get handle on workbooks
declare @xlWorkbooks int
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTPUT
--To open an existing one:
declare @xlWorkBook int
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, @srcFilename
-- Get the # of sheets and iterate 1 at a time
declare @numSheets integer
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'Sheets.Count' , @numSheets OUTPUT
-- Excel WorkSheet
declare @xlWorkSheet integer
-- Excel Cell
declare @xlCell integer
-- iVars for keeping track of which sheet is being processed
declare @currentSheet integer, @sheetSelect varchar(256)
-- iVars for the current Testname from A1 and the current prodMonth from A2
declare @Testname varchar(256), @pm varchar(50)
-- counters for the rows in the sheet we are interested in
declare @dataStartRow int, @dataEndRow int, @currentRow int
select @dataStartRow = 8, @dataEndRow = 38, @currentRow = 8
-- ivars/constants for the columns that hold tbb, test2, test3 data
declare @test1_col int, @test2_col int, @test3_col int
select @test1_col = 2, @test2_col = 3, @test3_col = 5
-- ivars for the data we want to store back to the DB
declare @test1 float, @test2 float, @test3 float
-- ivar to create the text for the range we are grabbing
declare @range varchar(256)
-- Loop through all available sheets and process 1 at a time
set @currentSheet = 0
while @currentSheet < @numSheets
begin
set @currentSheet = @currentSheet +1
set @sheetSelect = 'Sheets(' + cast(@currentSheet as varchar(10))+ ')'
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, @sheetSelect, @xlWorkSheet OUTPUT
-- Get Data from an existing Voucher for Testing
execute @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Range("A1").Value', @Testname OUTPUT
execute @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Range("A2").Value', @pm OUTPUT
set @currentRow = 8
-- loop throgh each set of rows on the current sheet
while @currentRow <= @dataEndRow
begin
select @range = 'Cells(' + cast(@currentRow as varchar(20)) + ',' + cast(@test1_col as varchar(20)) + ').Value'
execute @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test1 OUTPUT
select @range = 'Cells(' + cast(@currentRow as varchar(20)) + ',' + cast(@test2_col as varchar(20)) + ').Value'
execute @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test2 OUTPUT
select @range = 'Cells(' + cast(@currentRow as varchar(20)) + ',' + cast(@test3_col as varchar(20)) + ').Value'
execute @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test3 OUTPUT
-- TODO: write the data back to the database.... via Inserts
if @test1 + @test2 + @test3 > 0
begin
print 'Testname = ' + @Testname + ' PM = ' + @pm+ ' day =' + cast( (@currentRow-@dataStartRow + 1) as varchar(50)) + ' test1 = ' + cast(@test1 as varchar(50)) + ' test2 = ' + cast(@test2 as varchar(50)) + ' test3 = ' + cast(@test3 as varchar(50))
end
if @test1 + @test2 + @test3 > 0
begin
IF (EXISTS (select fname from testtable where fname = @Testname))
begin
UPDATE testtable
SET temp = @tbg,cast = @csg,volume = @MCF
WHERE testDate = @pm and day= (@currentRow-@dataStartRow + 1)and fname = @testname
end
else
begin
insert into testtable([Filename],fname,testdate,DOM,temp,cast,volume)
values
(@srcFilename,@testname,@PM,(@currentRow-@dataStartRow + 1),@test1,@test2,@test3)
end
end
-- increment counter and reset ivars for next pass
select @currentRow = @currentRow + 1, @test1 = 0, @test2 = 0, @test3 = 0
end
end
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'Close'
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Quit'
-- destroy all handles
execute @rs = master.dbo.sp_OADestroy @xlCell
execute @rs = master.dbo.sp_OADestroy @xlWorkSheet
execute @rs = master.dbo.sp_OADestroy @xlWorkBook
execute @rs = master.dbo.sp_OADestroy @xlWorkBooks
execute @rs = master.dbo.sp_OADestroy @xlApp
END
January 6, 2009 at 8:37 am
How many times does the PRINT statement execute?
Here's the whole thing reformatted a little for readability:
[font="Courier New"]ALTER PROCEDURE [dbo].[testproc]
@srcFilename VARCHAR(256)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- create an instance of the excel app
DECLARE @xlApp integer, @rs integer, @xlWorkBooks AS integer
EXECUTE @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT
-- turn off screen updates and prompts since we are remoting excel
EXECUTE @rs = MASTER.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
EXECUTE @rs = MASTER.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'
-- get handle on workbooks
DECLARE @xlWorkbooks INT
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTPUT
--To open an existing one:
DECLARE @xlWorkBook INT
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, @srcFilename
-- Get the # of sheets and iterate 1 at a time
DECLARE @numSheets integer
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlWorkBook, 'Sheets.Count' , @numSheets OUTPUT
-- Excel WorkSheet
DECLARE @xlWorkSheet integer
-- Excel Cell
DECLARE @xlCell integer
-- iVars for keeping track of which sheet is being processed
DECLARE @currentSheet integer, @sheetSelect VARCHAR(256)
-- iVars for the current Testname from A1 and the current prodMonth from A2
DECLARE @Testname VARCHAR(256), @PM VARCHAR(50)
-- counters for the rows in the sheet we are interested in
DECLARE @dataStartRow INT, @dataEndRow INT, @currentRow INT
SELECT @dataStartRow = 8, @dataEndRow = 38, @currentRow = 8
-- ivars/constants for the columns that hold tbb, test2, test3 data
DECLARE @test1_col INT, @test2_col INT, @test3_col INT
SELECT @test1_col = 2, @test2_col = 3, @test3_col = 5
-- ivars for the data we want to store back to the DB
DECLARE @test1 FLOAT, @test2 FLOAT, @test3 FLOAT
-- ivar to create the text for the range we are grabbing
DECLARE @range VARCHAR(256)
-- Loop through all available sheets and process 1 at a time
SET @currentSheet = 0
WHILE @currentSheet < @numSheets
BEGIN
SET @currentSheet = @currentSheet +1
SET @sheetSelect = 'Sheets(' + CAST(@currentSheet AS VARCHAR(10))+ ')'
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlWorkBook, @sheetSelect, @xlWorkSheet OUTPUT
-- Get Data from an existing Voucher for Testing
EXECUTE @rs = MASTER.dbo.sp_OAGetProperty @xlWorkSheet, 'Range("A1").Value', @Testname OUTPUT
EXECUTE @rs = MASTER.dbo.sp_OAGetProperty @xlWorkSheet, 'Range("A2").Value', @PM OUTPUT
SET @currentRow = 8
-- loop throgh each set of rows on the current sheet
WHILE @currentRow <= @dataEndRow
BEGIN
SELECT @range = 'Cells(' + CAST(@currentRow AS VARCHAR(20)) + ',' + CAST(@test1_col AS VARCHAR(20)) + ').Value'
EXECUTE @rs = MASTER.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test1 OUTPUT
SELECT @range = 'Cells(' + CAST(@currentRow AS VARCHAR(20)) + ',' + CAST(@test2_col AS VARCHAR(20)) + ').Value'
EXECUTE @rs = MASTER.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test2 OUTPUT
SELECT @range = 'Cells(' + CAST(@currentRow AS VARCHAR(20)) + ',' + CAST(@test3_col AS VARCHAR(20)) + ').Value'
EXECUTE @rs = MASTER.dbo.sp_OAGetProperty @xlWorkSheet, @range, @test3 OUTPUT
-- TODO: write the data back to the database.... via Inserts
IF @test1 + @test2 + @test3 > 0
BEGIN
PRINT 'Testname = ' + @Testname + ' PM = ' + @PM+ ' day =' + CAST( (@currentRow-@dataStartRow + 1) AS VARCHAR(50)) + ' test1 = ' + CAST(@test1 AS VARCHAR(50)) + ' test2 = ' + CAST(@test2 AS VARCHAR(50)) + ' test3 = ' + CAST(@test3 AS VARCHAR(50))
END
IF @test1 + @test2 + @test3 > 0
BEGIN
IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))
BEGIN
UPDATE testtable
SET TEMP = @tbg,CAST = @csg,volume = @mcf
WHERE testDate = @PM AND DAY= (@currentRow-@dataStartRow + 1)AND fname = @testname
END
ELSE
BEGIN
INSERT INTO testtable([Filename],fname,testdate,DOM,TEMP,CAST,volume)
VALUES
(@srcFilename,@testname,@PM,(@currentRow-@dataStartRow + 1),@test1,@test2,@test3)
END
END
-- increment counter and reset ivars for next pass
SELECT @currentRow = @currentRow + 1, @test1 = 0, @test2 = 0, @test3 = 0
END
END
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlWorkBook, 'Close'
EXECUTE @rs = MASTER.dbo.sp_OAMethod @xlApp, 'Quit'
-- destroy all handles
EXECUTE @rs = MASTER.dbo.sp_OADestroy @xlCell
EXECUTE @rs = MASTER.dbo.sp_OADestroy @xlWorkSheet
EXECUTE @rs = MASTER.dbo.sp_OADestroy @xlWorkBook
EXECUTE @rs = MASTER.dbo.sp_OADestroy @xlWorkBooks
EXECUTE @rs = MASTER.dbo.sp_OADestroy @xlApp
END
[/font]
Also, best not to use keywords as columnnames - if you must, then enclose within square brackets like this [CAST].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 8:41 am
thanks for reformating.
It needs to go on to end of last insert!!
Thanks
January 6, 2009 at 8:50 am
pat (1/6/2009)
thanks for reformating.It needs to go on to end of last insert!!
Thanks
What does?
How many times does the PRINT statement execute?
What values do you get for @test1, @test2 & @test3? If any of these evaluate to NULL,
then your table upsert won't work for that pass of the WHILE loop.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 9:01 am
I am surprised that this is working.
This line looks strange...
select @range = 'Cells(' + cast(@currentRow as varchar(20)) + ',' + cast(@test1_col as varchar(20)) + ').Value'
Normally you get ranges by specifying the column too, but its not available here.
@range now contains Cells(8,2).Value for first iteration.
I would imagine you want Cells(C8) to get this value..
But if you are getting 'some' result, then you could try setting:
set ansi_nulls off;
January 6, 2009 at 9:24 am
else
begin
While @test1 != 0 and @test2 != 0 and @test3 != 0
begin
insert into testtable([Filename],fname,testdate,DOM,temp,cast,volume)
values
(@srcFilename,@testname,@PM,(@currentRow-@dataStartRow + 1),@test1,@test2,@test3)
end
end
I tried that but it just goes on and on for ever!!
January 6, 2009 at 9:32 am
Pat, are you able to answer the questions?
Chris Morris (1/6/2009)
pat (1/6/2009)
thanks for reformating.It needs to go on to end of last insert!!
Thanks
What does?
How many times does the PRINT statement execute?
What values do you get for @test1, @test2 & @test3? If any of these evaluate to NULL,
then your table upsert won't work for that pass of the WHILE loop.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 9:42 am
the print statements gets all the records on the result set.
All three are getting value integer( or numbers) from the file.
January 6, 2009 at 9:52 am
Cool.
Now, change thisIF @test1 + @test2 + @test3 > 0
to this IF ISNULL(@test1, 0) + ISNULL(@test2, 0)+ ISNULL(@test3, 0)> 0
Also, put a new line in as follows...
IF ISNULL(@test1, 0) + ISNULL(@test2, 0)+ ISNULL(@test3, 0) > 0 -- <-- changed line
BEGIN
RAISERROR ('IF ISNULL(@test1, 0) + ISNULL(@test2, 0)+ ISNULL(@test3, 0)> 0',0,1) WITH NOWAIT -- <-- new line
IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 10:13 am
still the same it is not inserting all records. the file has 695 records and i only get 29 with initial setup or 204 after having is null criteria!!
January 6, 2009 at 10:39 am
pat (1/6/2009)
still the same it is not inserting all records. the file has 695 records and i only get 29 with initial setup or 204 after having is null criteria!!
You've got several possibilities on those conditionals. Put a RAISERROR with a relevant comment within each one.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 10:44 am
i only have two condition right now to do insert and update others are related to excel formating thanks
also if i do only insert statement it work like charm. it is only when i pass if the criterial or condition it just breaks or not performing fully with my requirements!
January 6, 2009 at 11:00 am
pat (1/6/2009)
i only have two condition right now to do insert and update others are related to excel formating thanksalso if i do only insert statement it work like charm. it is only when i pass if the criterial or condition it just breaks or not performing fully with my requirements!
You need to do a rowcount here... WHILE @currentRow <= @dataEndRow
and here...IF @test1 + @test2 + @test3 > 0
and for the two alternatives here...IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 11:05 am
i am blank now what do i need to do and how?
January 6, 2009 at 11:14 am
this is the sample file and teh data start from row 8 and ends at row 31
so the row count always be 31 form starting row 8 and ending row 31 on excel file!!
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply