problem in query

Question

Post reply

problem in query

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

pat (1/6/2009)
i am blank now what do i need to do and how?

Try this for starters IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))

BEGIN

RAISERROR ('UPDATE',0,1) WITH NOWAIT

UPDATE testtable

SET TEMP = @tbg,CAST = @csg,volume = @MCF

WHERE testDate = @pm AND DAY= (@currentRow-@dataStartRow + 1)AND fname = @testname

END

ELSE

BEGIN

RAISERROR ('INSERT',0,1) WITH NOWAIT

INSERT INTO testtable([Filename],fname,testdate,DOM,TEMP,CAST,volume)

VALUES

(@srcFilename,@testname,@PM,(@currentRow-@dataStartRow + 1),@test1,@test2,@test3)

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

here is the thing when i did that the you u suggest.

I only does one insert and rest of them update. and file has 29 worksheet so i get only 29 records.

which is the first record only!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

pat (1/6/2009)
here is the thing when i did that the you u suggest.
I only does one insert and rest of them update. and file has 29 worksheet so i get only 29 records.
which is the first record only!!

So...referring back to your original post, it looks like the part in bold is working (possibly), and something else isn't.

How many updates and inserts do you get for each worksheet? Count "UPDATE" and "INSERT" written by RAISERROR.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 4

Hi,

i did that already and that's why i said that it only does one insert per worksheet(total count of worksheets are 29) and it updates the same reocords.

thanks

Pat

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

pat (1/6/2009)
Hi,
i did that already and that's why i said that it only does one insert per worksheet(total count of worksheets are 29) and it updates the same reocords.
thanks
Pat

So it does one INSERT and one UPDATE per worksheet?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 6

no i does on insert but rest of them update like if worksheet has data till 24 rows it will run update till 24 but insert is one only!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

pat (1/6/2009)
no i does on insert but rest of them update like if worksheet has data till 24 rows it will run update till 24 but insert is one only!!

The PRINT statement print 'Testname = ' + @Testname +

What is printed for @Testname for each row?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 8

hey,

it also prints the name as well.

this is so wierd now.. if i put the print message on insert it does all insert but not update and it duplicates the recodrs 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Yes, it does. Is it always the same?

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 10

pat-261672

Hall of Fame

Points: 3325

January 6, 2009 at 12:50 pm

#922076

yes

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

pat (1/6/2009)
yes

Top work Pat, you've cracked it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 12

pat-261672

Hall of Fame

Points: 3325

January 6, 2009 at 12:56 pm

#922086

what?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

pat (1/6/2009)
what?

@Testname is assigned only outside the WHILE loop (WHILE @currentRow <= @dataEndRow) so remains static for each iteration.

This conditional ...IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))

... says: IF there's already a row where fname = @Testname then update it, otherwise insert it. Since @Testname is the same for all rows, only the first will be inserted - the rest will update.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

pat-261672 Hall of Fame Points: 3325 More actions · Answer 14

then how will i write the insert and update please guide me!!

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

pat (1/6/2009)
then how will i write the insert and update please guide me!!
thanks

Sure! Remember that PRINT statement? Post what gets printed, say the first 10 lines for one worksheet.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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