check sql licence: rows

Showing posts with label rows. Show all posts

Tuesday, March 27, 2012

Clear the resultset during batch processing

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegrou ps.com...
|||Hello Falik,
Thanks for showing a helping hand

> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>
sql sql

Clear the resultset during batch processing

Hello SQL Experts,
I have a Batch of Select statments which runs in a while loop and i use
the @.@.rowcount to get the rows returned to manipulate furture.
the problem is after some time in the middle of the process i get the
below error
"The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid."
Is there a way by which i can stop the results being displayed in the
message tab or clear the diplayed result or result set as said in the
error messge. as after the error message the task stops on it own.
i dont know if there is any thing using SET statment
/sonihi,
This means essentially you do not need these results generated by the SQLs?
is this the case? can you send some sample SQLs so something can be
suggested close to your needs.
rgds
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> Hello SQL Experts,
> I have a Batch of Select statments which runs in a while loop and i use
> the @.@.rowcount to get the rows returned to manipulate furture.
> the problem is after some time in the middle of the process i get the
> below error
>
> "The query has exceeded the maximum number of result sets that can be
> displayed in the results grid. Only the first 100 result sets are
> displayed in the grid."
> Is there a way by which i can stop the results being displayed in the
> message tab or clear the diplayed result or result set as said in the
> error messge. as after the error message the task stops on it own.
> i dont know if there is any thing using SET statment
> /soni
>|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand :)
> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>> hi,
>> This means essentially you do not need these results generated by the
>> SQLs?
>> is this the case? can you send some sample SQLs so something can be
>> suggested close to your needs.
>> rgds
>> Falik
>>
>> <chandresh.x.soni@.sonyericsson.com> wrote in message
>> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
>> > Hello SQL Experts,
>> >
>> > I have a Batch of Select statments which runs in a while loop and i use
>> > the @.@.rowcount to get the rows returned to manipulate furture.
>> >
>> > the problem is after some time in the middle of the process i get the
>> > below error
>> >
>> >
>> > "The query has exceeded the maximum number of result sets that can be
>> > displayed in the results grid. Only the first 100 result sets are
>> > displayed in the grid."
>> >
>> > Is there a way by which i can stop the results being displayed in the
>> > message tab or clear the diplayed result or result set as said in the
>> > error messge. as after the error message the task stops on it own.
>> >
>> > i dont know if there is any thing using SET statment
>> >
>> > /soni
>> >
>

Clear the resultset during batch processing

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hello Falik,
Thanks for showing a helping hand

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand

cleansing data

I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.

-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")

Madhivanan

Sunday, March 25, 2012

cleaning different formats

I have a column "currentDate" with approx 4 million rows.
The rows are composed of dates , but with a variety of formats.
For example:
"14-05-2005 14:56:32"
"20/07/2005 10:26:43"
"2006-03-26 11:21:42"
I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
hh:mm:ss, and then convert the column into a datetime
The problem I have is that I am using DATEPART , which is fine , except that
I am losing the leading zero.
Taking "02/03/2005 10:26:43" as an example ,
when I do the following:
CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to "02"
,
How can I retain the leading "0" , for any DATEPART.?>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
Bravo! But that is very far from a standard format!
Why wasn't this a datetime in the first place? What application is storing
all these different formats? Are you allowing end users to just enter
whatever format they want?
Assuming these are the only three formats present, this might give you some
ideas:
SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
FROM
(
SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
ELSE CONVERT(DATETIME, d, 103) END
FROM
(
SELECT d = '14-05-2005 14:56:32'
UNION ALL SELECT '20/07/2005 10:26:43'
UNION ALL SELECT '2006-03-26 11:21:42'
) a
) b
However, I am betting there are twenty other formats you're not mentioning.
A|||You may try this:
select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
Perayu
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||Jack Vamvas (delete_this_bit_jack@.ciquery.com_delete) writes:
> I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e
> dd-mm-yyyy hh:mm:ss, and then convert the column into a datetime The
> problem I have is that I am using DATEPART , which is fine , except that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02" ,
> How can I retain the leading "0" , for any DATEPART.?
There are a couple of variations on that theme, but it does not seem to
address your real issue anyway.
Obviously you have dateformat of dmy, in which case the format 2006-03-26
is not likely to convert to datetime.
I would suggest that you add a new column to the table, nullable. Then
you would do something like:
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
go
SET DATEFORMAT ymd
go
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
AND newcol IS NULL
go
SELECT * FROM tbl WHERE newcol IS NULL
-- Manuallly fix the rest?
go
ALTER TABLE tbl DROP oldcol
go
-- If column should not permit NULL.
ALTER TABLE col ALTER newcol datetime NOT NULL
One thing you would have to make an extra check for are completely far-out
date formats like MM/DD/YY (yes, there are odd corners of the world where
they use this). Not talking of a string like 05/03/02 that has a number of
interpretations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The Convert function will do the conversion to DateTime for you. However,
depending on the format, you will need to specify the Style parameter.
Lookup the Convert function in SQL Server Books Online. For example:
-- Italian dd-mm-yy
print convert(datetime,'14-05-2005 14:56:32',105)
May 14 2005 2:56PM
print convert(datetime,'14/05/2005 14:56:32',105)
May 14 2005 2:56PM
-- ANSI
print convert(datetime,'2005-05-14 14:56:32',102)
May 14 2005 2:56PM
Also, going forward, you will need to constrain user input into the
application to a consistent format.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#yCoM86OGHA.2912@.tk2msftngp13.phx.gbl...
dd-mm-yyyy
> Bravo! But that is very far from a standard format!
> Why wasn't this a datetime in the first place? What application is
storing
> all these different formats? Are you allowing end users to just enter
> whatever format they want?
> Assuming these are the only three formats present, this might give you
some
> ideas:
> SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
> FROM
> (
> SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
> ELSE CONVERT(DATETIME, d, 103) END
> FROM
> (
> SELECT d = '14-05-2005 14:56:32'
> UNION ALL SELECT '20/07/2005 10:26:43'
> UNION ALL SELECT '2006-03-26 11:21:42'
> ) a
> ) b
> However, I am betting there are twenty other formats you're not
mentioning.
> A
>
>
>
Thanks for the response.
Just to clarify , this data is inherited from a client . It is historical
data , and the different formats represent different pahases of their data
recording.
They dumped everything into a varchar column and now are requesting
different queries based on the dates.Hence the need to sort this problem
out.
The 3 formats I've presented are the only formats. The code you've presented
has givem me some ideas.|||> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
I think you can eliminate this intermediate step. Just convert from the
existing values into DateTime.|||Thanks
That sorted the problem out
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uZ#VY86OGHA.1132@.TK2MSFTNGP10.phx.gbl...
> You may try this:
> select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
> Perayu
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
dd-mm-yyyy
>|||I was having a similar issue and this fixed my problem as well! Good tip!

Thursday, March 22, 2012

Clarification on the ROWCOUNT statement

Hi All,
I just wanted to get some clarification on the ROWCOUNT statement in T-SQL.
I'm presently using it to limit the number of rows returned in SELECT based
SPs.
However I'm just curious as the effect of this keyword on the global scope
of SQL operations.
Does setting ROWCOUNT to 25 in one stored procedure effect other SPs running
?
Or is it only specific to the SP which set it?
Also...if it does only effect the code in the specific SP which set the
ROWCOUNT why is it recommended/required to set it back to zero when you are
done?
I have tested this and is "seems" to have no effect on other code running,
but I just want to make sure before I create considerable headaches in my
application.
Thanks for any feedback.
John RossitterJohn,
You can just use the TOP clause with an ORDER BY clause to ommit the need of
setting ROWCOUNT.
From SQL BOL:
It is recommended that DELETE, INSERT, and UPDATE statements currently using
SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
DELETE, INSERT, or UPDATE.
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
DELETE statements against remote tables and local and remote partitioned
views.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in
> T-SQL.
> I'm presently using it to limit the number of rows returned in SELECT
> based
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs
> running?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you
> are
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||The ROWCOUNT setting does not bleed to other connections and it is also loca
l to inside the
procedure. I still always type a comment to reset it to 0 the very same mome
nt as I type the setting
to non-zero. Just in case someone else add some code to the procedure later.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in T-SQL
.
> I'm presently using it to limit the number of rows returned in SELECT base
d
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs runni
ng?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you ar
e
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||Hi Jerry,
I can't use TOP because I need a dynamic number of rows selected.
Which is why I'm using ROWCOUNT directive instead.
To clarify what I have done, is in one of my tables set a MaxRows threshold.
Now when any of my SELECT based SPs execute the code looks something like
this:
====================================
DECLARE @.TOP int;
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
SET ROWCOUNT @.TOP
SELECT ....
SET ROWCOUNT 0
=====================================
This way I can control the overall performance of all of my queries.
I just want to make sure that the ROWCOUNT setting does not have an impact
on any other code running in SQL.
For example lets say that the above SP was running, and then another started
in the middle of it's execution. Should I expect to see the ROWCOUNT clause
effect the 2nd query, or does it only effect the code which it's currently
executing.
I hope that helps clarify my question.
Thanks,
John Rossitter
"Jerry Spivey" wrote:

> John,
> You can just use the TOP clause with an ORDER BY clause to ommit the need
of
> setting ROWCOUNT.
> From SQL BOL:
> It is recommended that DELETE, INSERT, and UPDATE statements currently usi
ng
> SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
> DELETE, INSERT, or UPDATE.
> The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
> DELETE statements against remote tables and local and remote partitioned
> views.
> HTH
> Jerry
> "John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in messag
e
> news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
>
>|||Ok. I'm not aware of any issues. My testing confirms the same results - no
side affects. However that being said, I would recommend appending a SET
ROWCOUNT 0 to your code for completeness.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:9596F2C7-649D-4EDD-98B3-A7C321FD4038@.microsoft.com...
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows
> threshold.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another
> started
> in the middle of it's execution. Should I expect to see the ROWCOUNT
> clause
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>|||Hi
You can use top in dynamic queries like this
DECLARE @.TOP int;
DECLARE @.VAR varchar(200)
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
select @.VAR = 'SELECT TOP' + CAST(@.TOP as varchar(10) + ' from your table
name...'
exec(@.VAR)
--
Regards
R.D
--Knowledge gets doubled when shared
"John Rossitter" wrote:
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows threshol
d.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another start
ed
> in the middle of it's execution. Should I expect to see the ROWCOUNT claus
e
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>

Friday, February 24, 2012

Checking to see if a records exists before inserting - 3 million + rows

I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.

I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.

To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.

Any thoughts would be great!

David Wynne wrote:

Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

You aren't doing a select * against the lookup table, are you?|||Of course not - just pseudo code to get across what I think I'd like to achieve.
|||

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

|||

Charles Talleyrand wrote:

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

Yep, the OP could use an Execute SQL task to load up a temporary table with the keys of the lookup table. Then, in the data flow, you can use the mentioned outer join to capture new records. That is, unless you need to repopulate the lookup table with the incoming "new" records before the next lookup.|||

David,

How many columns do you need to put in the lookup transformation to determine if a record exists? Unless we are taking about too many/wide columns; I don't see several millions to be a problem. Another option is to use partial cache with decent amount of memory; so the chances a row is not in cache is low.

Remember, always provide a query with only the columns that are strictly required for the join

Checking obligated values in different rows

Hi there!

I have a problem I can't get rid of... I hope anyone can help me with it!

For a room-booking application I need to select available rooms for a given
period. First I do the availability check which is no problem, than I need
to find out if there is a tariff available for every night of the given
period. If not, I will not show the room as 'available' in the system
because I can't make a tariff calculation.

All tariffs are entered in a table which can hold different night-tariffs
for different periods. The table looks like this:

========
CREATE TABLE [dbo].[TARIEVEN] (
[TARIEF_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FK_OBJECT_ID] [numeric](18, 0) NOT NULL ,
[STARTDATUM] [datetime] NULL ,
[EINDDATUM] [datetime] NULL ,
[TARIEF_PRIJS] [decimal](10, 2) NULL ,
) ON [PRIMARY]
GO

FK_OBJECT_ID is the Room ID
STARTDATUM is the startdate of a tariff period
EINDDATUM is the enddate of a tariff period
TARIEF_PRIJS is the tariff per night in the tariff period
========

What I do at the moment is:
---
SELECT fk_object_id FROM TARIEVEN
where ('15-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
AND ('16-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
AND ('17-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
---

This will work if all nights are in the same tariff period, but it will not
produce the right results if the tariffs are in different periods because no
single row contains a startdate and enddate in between which all dates are.
I can't use an OR statement because I absolutely need a tariff per night.

So, what my question is in short:
How can I for example select OBJECT 1 from the following table considering
that I want to return all rooms which have a tariff available for a period
from 15th of october to 17th of october?

------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 17-oct-2004 18-oct-2004
125

In words: object 1 costs 100 per night in the period from 10 to 16 october
and 125 in the period from 17 to 18 october
------

I hope anyone can help me. Thanks a lot in advance!!

RobertI will assume that the Tarieven table won't contain overlapping dates, in
other words there can only be a single tariff per room per day.

DECLARE @.start_dt DATETIME, @.eind_dt DATETIME

/* The required date range: */
SET @.start_dt = '20041015'
SET @.eind_dt = '20041017'

SELECT @.start_dt, @.eind_dt,
fk_object_id, MIN(tarief_prijs) AS tarief_prijs
FROM Tarieven
WHERE einddatum >= @.start_dt
AND startdatum <= @.eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prijs) = MAX(tarief_prijs)
AND MIN(startdatum) <= @.start_dt
AND MAX(einddatum) >= @.eind_dt

HAVING MIN(tarief_prijs) = MAX(tarief_prijs) ensures a single tariff.

The last two predicates exclude the case where the Tarieven table only
includes rows for part of the required period - in other words there was
only a single tariff for that room but it didn't cover the whole of the
required period.

--
David Portas
SQL Server MVP
--|||Brilliant!

Thanks a lot David!! You saved my life!

Checking obligated values in different rows

Hi there!

I have a problem I can't get rid of... I hope anyone can help me with it!

All tariffs are entered in a table which can hold different night-tariffs
for different periods. The table looks like this:

FK_OBJECT_ID is the Room ID
STARTDATUM is the startdate of a tariff period
EINDDATUM is the enddate of a tariff period
TARIEF_PRIJS is the tariff per night in the tariff period
========

------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 17-oct-2004 18-oct-2004
125

In words: object 1 costs 100 per night in the period from 10 to 16 october
and 125 in the period from 17 to 18 october
------

I hope anyone can help me. Thanks a lot in advance!!

RobertI will assume that the Tarieven table won't contain overlapping dates, in
other words there can only be a single tariff per room per day.

DECLARE @.start_dt DATETIME, @.eind_dt DATETIME

/* The required date range: */
SET @.start_dt = '20041015'
SET @.eind_dt = '20041017'

HAVING MIN(tarief_prijs) = MAX(tarief_prijs) ensures a single tariff.

--
David Portas
SQL Server MVP
--|||Brilliant!

Thanks a lot David!! You saved my life!|||Hi David,

I hope I can use your brains again :-)

Your solution works perfectly except for one thing. If there is a gap in the
tariff information it will still return the object. Do you know of a
possibility to fix that?

Imagine the following information:

I'm trying to book a room from 12-oct-2004 to 21-oct-2004.

This is what the tariff table looks like
------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 19-oct-2004 22-oct-2004
125
------

There is no tariff information for the nights of 17 and 18 october, so the
object should not be returned as a valid object (no price calculation can be
made for those nights). Is there a way to do this?

Hope you can help me again!

Robert|||First create a Calendar table to help with this one.

CREATE TABLE Calendar
(caldate DATETIME PRIMARY KEY)

Populate it:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20201231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Now you can do this:

SELECT @.start_dt, @.eind_dt,
fk_object_id, MIN(tarief_prijs) AS tarief_prijs
FROM Tarieven AS T
JOIN Calendar AS C
ON C.caldate
BETWEEN T.startdatum AND T.einddatum
AND C.caldate
BETWEEN @.start_dt AND @.eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prijs) = MAX(tarief_prijs)
AND COUNT(DISTINCT C.caldate) > DATEDIFF(DAY,@.start_dt,@.eind_dt)

--
David Portas
SQL Server MVP
--|||Hi David,

I hope I can use your brains again :-)

Your solution works perfectly except for one thing. If there is a gap in the
tariff information it will still return the object. Do you know of a
possibility to fix that?

Imagine the following information:

I'm trying to book a room from 12-oct-2004 to 21-oct-2004.

This is what the tariff table looks like
------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 19-oct-2004 22-oct-2004
125
------

Hope you can help me again!

Robert|||First create a Calendar table to help with this one.

CREATE TABLE Calendar
(caldate DATETIME PRIMARY KEY)

Populate it:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20201231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Now you can do this:

--
David Portas
SQL Server MVP
--|||Great! Thanks for saving my life again :-)

Thanks a lot!|||Great! Thanks for saving my life again :-)

Thanks a lot!

Tuesday, March 27, 2012

Clear the resultset during batch processing

Clear the resultset during batch processing

Clear the resultset during batch processing

cleansing data

Sunday, March 25, 2012

cleaning different formats

Thursday, March 22, 2012

Clarification on the ROWCOUNT statement

Friday, February 24, 2012

Checking to see if a records exists before inserting - 3 million + rows

Checking obligated values in different rows

Checking obligated values in different rows

check sql licence

Blog Archive

About Me