Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?
Any help would be appreciated!
Brian@.@.ROWCOUNT stores the number of rows affected by the last statement. It is continually changing, so you may need to store the value in another variable immediately after your statement is executed.
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
rowdelimiter not accepted in bulk insert statement , used in an sproc - please help
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RS
From BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.
> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Hi Mike
Your answer solves my the question i had in mind.
2. What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adXminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format file.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically..."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets insertedvia
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
>
> If you run the above code with '\n' as the row terminator and BULK INSERTis
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can provide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?Try
> BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
>)
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
sql
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RS
From BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.
> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Hi Mike
Your answer solves my the question i had in mind.
2. What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adXminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format file.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically..."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets insertedvia
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
>
> If you run the above code with '\n' as the row terminator and BULK INSERTis
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can provide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?Try
> BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
>)
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
sql
rowdelimiter not accepted in bulk insert statement , used in an sproc - please help
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
--
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RSFrom BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.
> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>|||Hi Mike
Your answer solves my the question i had in mind.
2=2E What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\=ad=ADminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format fi=le.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically=.=2E."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets inserted= via
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
> > So i run the above code and it inserts rows into table. No data is
> > present in last column.
> If you run the above code with '\n' as the row terminator and BULK INSERT= is
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can prov=ide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?= Try
> BULK INSERT with FIRSTROW =3D1 and LASTROW=3D1 so that you only insert th=e first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
> > BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> > WITH
> > (
> > FIELDTERMINATOR =3D ';',
> > ROWTERMINATOR =3D '\n'
> > )
> > --
> > This is the query used to populate bill_tbl.
> > Actually this baddress.dat contain rowdelimiter of \r\n.
> > This can be seen by viewing the file in hex format (OD,OA) and also
> > the format file created by the bulk insert task of dts gives the last
> > row as \r\n
> > So i run the above code and it inserts rows into table. No data is
> > present in last column.
> > The above bulk insert stmt should leave a carriage return in the sql
> > table, but i see len is zero as well as i query for it for no avail.
> > 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> > goes fine, just like above, but again len is zero and i do not see
> > that it has imported the {CR} character.
> > 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> > goes fine. imports same number of rows and len of last col is zero
> > 4. So i run another bulk insert with {CR} as row delimiter, i get an
> > error stating : conversion error for first column - makes sense as it
> > is trying to insert {LF} in first col and the first col size is 1.
> > 5.So the main problem is in the above stmt, i put \r\n, it does not
> > work. I am not sure why.
> > I proved it works in the dts. The above code lies in a sproc and is
> > already written and being used, but they have suddenly discovered they
> > are having special characters when they try to import the table into a
> > text file and having problems.
> > So i would like to keep above code but introduce \r\n as row
> > delimiter. Can anyone tell me why it is not working ?
> > thanks
> > RS- Hide quoted text -
> - Show quoted text -|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
>)
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
--
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RSFrom BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.
> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:
> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>|||Hi Mike
Your answer solves my the question i had in mind.
2=2E What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\=ad=ADminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format fi=le.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically=.=2E."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets inserted= via
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
> > So i run the above code and it inserts rows into table. No data is
> > present in last column.
> If you run the above code with '\n' as the row terminator and BULK INSERT= is
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can prov=ide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?= Try
> BULK INSERT with FIRSTROW =3D1 and LASTROW=3D1 so that you only insert th=e first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
> > BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> > WITH
> > (
> > FIELDTERMINATOR =3D ';',
> > ROWTERMINATOR =3D '\n'
> > )
> > --
> > This is the query used to populate bill_tbl.
> > Actually this baddress.dat contain rowdelimiter of \r\n.
> > This can be seen by viewing the file in hex format (OD,OA) and also
> > the format file created by the bulk insert task of dts gives the last
> > row as \r\n
> > So i run the above code and it inserts rows into table. No data is
> > present in last column.
> > The above bulk insert stmt should leave a carriage return in the sql
> > table, but i see len is zero as well as i query for it for no avail.
> > 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> > goes fine, just like above, but again len is zero and i do not see
> > that it has imported the {CR} character.
> > 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> > goes fine. imports same number of rows and len of last col is zero
> > 4. So i run another bulk insert with {CR} as row delimiter, i get an
> > error stating : conversion error for first column - makes sense as it
> > is trying to insert {LF} in first col and the first col size is 1.
> > 5.So the main problem is in the above stmt, i put \r\n, it does not
> > work. I am not sure why.
> > I proved it works in the dts. The above code lies in a sproc and is
> > already written and being used, but they have suddenly discovered they
> > are having special characters when they try to import the table into a
> > text file and having problems.
> > So i would like to keep above code but introduce \r\n as row
> > delimiter. Can anyone tell me why it is not working ?
> > thanks
> > RS- Hide quoted text -
> - Show quoted text -|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
>)
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
rowdelimiter not accepted in bulk insert statement , used in an sproc - please help
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, an
d it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RS>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
> )
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, a
nd it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, an
d it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RS>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:
>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
> )
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, a
nd it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
Monday, March 26, 2012
Row_number selecting from a complex select statement
Hi,
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
sql
Code Snippet
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)
select * from myTableWithRowNum
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
The following query might help you,
Code Snippet
;with UnionResult(myvalue,insertdate)
as
(
select table1Id As myValue,insertdate from myTable1
union
select table2Id As myValue,insertdate from myTable2
),
OrderedResult(myValue,Row)
as
(
select myValue, row_number() over (order by insertdate desc)
from UnionResult
)
select * from OrderedResult
|||I m not sure I understand your requirment correctly,anyhow your query throws error,
Try the following
Code Snippet
;with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select insertdate,table1Id As myValue from myTable1
union
select insertdate,table2Id As myValue from myTable2
) as temp
)
select * from myTableWithRowNum
|||Thanks that's exactly what I'm looking for.sql
Friday, March 23, 2012
Row to Column?
All:
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col
Row Order on View Results
When I run a view on SQL 2005 the resulting rows are not in order, even if the SQL statement defining the view includes an order by clause.
Within the Microsoft SQL Manager Studio (SQL 2005), when the view is opened, the rows are in no specific order.
Records viewed remotely via ADO likewise are not displayed in order. Neither are records viewed via ODBC.
Interestingly, when opened in modify mode within the Microsoft SQL Manager Studio (SQL 2005), the view does display the records according to the ORDER BY clause.
On the other hand, the same view on SQL 2000 produces result sets organized according to the ORDER BY clause. This is true whether the view is opened normally or in design mode.
And records viewed remotely via ADO are displayed in order, as are records viewed via ODBC.
I find this disappointing and a stumbling block in moving databases out of SQL 2000 and into SQL 2005.
The Database that I used was one pulled into a SQL 2005 64 bit server out of a back up made by a SQL 2000 server of a SQL 2000 database.
In general it's not recommended to include an ORDER BY clause in a view. A view should define a new relation of attributes derived from existing attributes in the datamodel. A query using the view should apply an order by on the data represented by the view to produce an ordered resultset.Wednesday, March 21, 2012
Row number in a select statement
Hi
I am looking for a solution to get an incremental row number along my other
select arguments after joining more than one tables.
Thanks in advance.
Ashokuse pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashok" <akumar@.buildfolio.com> wrote in message
news:uweREUBFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am looking for a solution to get an incremental row number along my
> other
> select arguments after joining more than one tables.
> Thanks in advance.
> Ashok
>
>|||Why can't you do it client-side? That's probably much more efficient
than attempting it in SQL.
If you really must, the following is one example of a SQL-based method
(from Pubs). You've said that more than one table is involved but it's
hard to give a full answer for that more complex scenario without more
info - like the DDL for the tables involved and some sample data to
work with.
SELECT au_id, au_lname, au_fname,
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num
FROM Authors AS A
David Portas
SQL Server MVP
--
I am looking for a solution to get an incremental row number along my other
select arguments after joining more than one tables.
Thanks in advance.
Ashokuse pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashok" <akumar@.buildfolio.com> wrote in message
news:uweREUBFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am looking for a solution to get an incremental row number along my
> other
> select arguments after joining more than one tables.
> Thanks in advance.
> Ashok
>
>|||Why can't you do it client-side? That's probably much more efficient
than attempting it in SQL.
If you really must, the following is one example of a SQL-based method
(from Pubs). You've said that more than one table is involved but it's
hard to give a full answer for that more complex scenario without more
info - like the DDL for the tables involved and some sample data to
work with.
SELECT au_id, au_lname, au_fname,
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num
FROM Authors AS A
David Portas
SQL Server MVP
--
Row Number
Hi everyone
i want to retrive the row number of result of "Select" Statement.
Thanks
MehdiMehdi wrote:
> Hi everyone
> i want to retrive the row number of result of "Select" Statement.
> Thanks
> Mehdi
That's a slightly obscure question. It could mean any of several
things:
You want to iterate a result set and return a number for some row - You
can do that client side using ADO recordsets.
You want to know the number of rows returned by the last SELECT
statement - The @.@.ROWCOUNT function will tell you that.
You want to return a row number for each row in a SELECT statement -
Use the ROW_NUMBER, RANK or DENSE_RANK function in your query (assumes
SQL Server 2005).
Something else? Then give us some more detail please.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
i want to retrive the row number of result of "Select" Statement.
Thanks
MehdiMehdi wrote:
> Hi everyone
> i want to retrive the row number of result of "Select" Statement.
> Thanks
> Mehdi
That's a slightly obscure question. It could mean any of several
things:
You want to iterate a result set and return a number for some row - You
can do that client side using ADO recordsets.
You want to know the number of rows returned by the last SELECT
statement - The @.@.ROWCOUNT function will tell you that.
You want to return a row number for each row in a SELECT statement -
Use the ROW_NUMBER, RANK or DENSE_RANK function in your query (assumes
SQL Server 2005).
Something else? Then give us some more detail please.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would i
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
Monday, March 12, 2012
Row IDs in resultset
Hi,
I would like to get Row IDs to number my resultset from 1 to whatever. For
example, if I have 10 records from the following statement :
select FirstName, LastName from employees
I would like to number the records from 1 to 10. How do I do it? No cursor
please.
TIAhttp://www.aspfaq.com/show.asp?id=2427
Note, this does not include any information about SQL Server 2005's
ROW_NUMBER function, which makes this whole process much easier...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||> http://www.aspfaq.com/show.asp?id=2427
> Note, this does not include any information about SQL Server 2005's
> ROW_NUMBER function, which makes this whole process much easier...
Hey man, how many hands do you think I have? :-)|||Based on the amount of information on the site, I'd say somewhere between
four and six?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXowA684FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hey man, how many hands do you think I have? :-)
>|||Method 1:
If one of the columns in query is unique, the following calculates a
sequential number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
Method 2:
If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||More like 17. :)
ML
I would like to get Row IDs to number my resultset from 1 to whatever. For
example, if I have 10 records from the following statement :
select FirstName, LastName from employees
I would like to number the records from 1 to 10. How do I do it? No cursor
please.
TIAhttp://www.aspfaq.com/show.asp?id=2427
Note, this does not include any information about SQL Server 2005's
ROW_NUMBER function, which makes this whole process much easier...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||> http://www.aspfaq.com/show.asp?id=2427
> Note, this does not include any information about SQL Server 2005's
> ROW_NUMBER function, which makes this whole process much easier...
Hey man, how many hands do you think I have? :-)|||Based on the amount of information on the site, I'd say somewhere between
four and six?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXowA684FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hey man, how many hands do you think I have? :-)
>|||Method 1:
If one of the columns in query is unique, the following calculates a
sequential number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
Method 2:
If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||More like 17. :)
ML
Friday, March 9, 2012
row column repetition help
I've made a view from a (complex) select statement of 4 tables in my database.
lest say I get results like these...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
patient1 med2 usage2 diagnum
patient1 med3 usage3 diagnum
I would like to cortrect my query so that the result is the following...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
med2 usage2
med3 usage3
since the patient number is the same and the diag num is the same
i want ot be able to avoid the repetition...
this might not help but heres is my original query
CREATE OR REPLACE VIEW PRESCRIPTIONS AS
SELECT DISTINCT d.NoAssMaladie, p.prenompatient || ' ' || UPPER(p.nompatient) AS NomPatient, l.nomedicament AS NoMedic,m.libmedicament AS Libelle, l.quantite || ',' || l.prises || ',' || l.duree AS "Desc. d.usage", l.nodiagnostic AS Diag
FROM lignes_prescriptions l, patients p, diagnostics d, medicaments m
WHERE l.nodiagnostic = d.nodiagnostic AND
d.noassmaladie = p.noassmaladie AND
m.nomedicament = l.nomedicament AND
d.RESULTATDIAGNOSTIC = 'P' AND
d.NoAssMaladie = 'SANL 6005 1218'
where my patient is 'SANL 6005 1218'
And secondly I would like to know how to make a view that will ask for a value that I can apply to my where statement.
lets say I wanted to ask for the patient number 'SANL 6005 1218' instead of putting it into my query directly.Technically speaking the view you created contains no duplicates, where a duplicate is defined as the row projected from the select statement. If you select the primary key then the rows are already distinct. I don't think you can return 4 columns with different row depths as the dbms would not know what to place in the 'Empty' cells.|||You should check your schema and ensure the FD's are correct in your view for example the view you created leeds one to believe that possibly column1, column2, column3 are the key with column4 being dependent on this key.|||Hi,
First you cannot give parameters to a VIEW as they are just structures stored with no data.
When you query the view you should include the parameter in the where clause.
As for your requirement, you should use SQL REPORTING utility to get the report in that format.
use the following commands
BREAK ON PATIENT_ID ON DIAGRAMID
SELECT PATIENT_ID, DIAGRAMID ,....
FROM <<VIEW NAME>> ORDER BY PATIENT_ID, DIAGRAMID
If needed spool the result into a text file.
then use CLEAR BREAKS command to clear the break settings.
Regars
Shelva
lest say I get results like these...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
patient1 med2 usage2 diagnum
patient1 med3 usage3 diagnum
I would like to cortrect my query so that the result is the following...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
med2 usage2
med3 usage3
since the patient number is the same and the diag num is the same
i want ot be able to avoid the repetition...
this might not help but heres is my original query
CREATE OR REPLACE VIEW PRESCRIPTIONS AS
SELECT DISTINCT d.NoAssMaladie, p.prenompatient || ' ' || UPPER(p.nompatient) AS NomPatient, l.nomedicament AS NoMedic,m.libmedicament AS Libelle, l.quantite || ',' || l.prises || ',' || l.duree AS "Desc. d.usage", l.nodiagnostic AS Diag
FROM lignes_prescriptions l, patients p, diagnostics d, medicaments m
WHERE l.nodiagnostic = d.nodiagnostic AND
d.noassmaladie = p.noassmaladie AND
m.nomedicament = l.nomedicament AND
d.RESULTATDIAGNOSTIC = 'P' AND
d.NoAssMaladie = 'SANL 6005 1218'
where my patient is 'SANL 6005 1218'
And secondly I would like to know how to make a view that will ask for a value that I can apply to my where statement.
lets say I wanted to ask for the patient number 'SANL 6005 1218' instead of putting it into my query directly.Technically speaking the view you created contains no duplicates, where a duplicate is defined as the row projected from the select statement. If you select the primary key then the rows are already distinct. I don't think you can return 4 columns with different row depths as the dbms would not know what to place in the 'Empty' cells.|||You should check your schema and ensure the FD's are correct in your view for example the view you created leeds one to believe that possibly column1, column2, column3 are the key with column4 being dependent on this key.|||Hi,
First you cannot give parameters to a VIEW as they are just structures stored with no data.
When you query the view you should include the parameter in the where clause.
As for your requirement, you should use SQL REPORTING utility to get the report in that format.
use the following commands
BREAK ON PATIENT_ID ON DIAGRAMID
SELECT PATIENT_ID, DIAGRAMID ,....
FROM <<VIEW NAME>> ORDER BY PATIENT_ID, DIAGRAMID
If needed spool the result into a text file.
then use CLEAR BREAKS command to clear the break settings.
Regars
Shelva
Wednesday, March 7, 2012
Rounding problems
I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
rounding numerics
Hello,
Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * (1 - @.test2)
Changing the type of @.test2 to VARCHAR(12,10) corrects the problem. I don't understand why SQL Server does this rounding?
Thanx,
Wouter
Hi,
You need to change the script like this to avoid an implicit cast to an integer:
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * cast(1 - @.test2 as NUMERIC(24,10))
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||At least part of the answer lies with the fact that the "1" in the statement:
select @.test * (1 - @.test2)
is an integer datatype and must be converted for all of the operations to take place. This can be avoided by performing explicit converting to the numeric(24,10) datatype such as:
select @.test * convert (numeric(24,10), (convert (numeric(24, 10),1) - @.test2)) as converted
-- converted
-- -
-- 9.999999999
Also beware that converting to a float might not be exactly what you want either:
|||{ Obviously, I am in agreement with Geert. :-) }select @.test * convert (float,1) - @.test2 as floater
-- floater
-- --
-- 9.9999999989999999
Saturday, February 25, 2012
Round Statement Incorrect Value
Hello,
I am having trouble getting the correct calculation with the statement below. The error is that QTR4 is being divided by Select SUM instead of all 4 quarters. I have tried closing the addition statements but get errors on all scenarios that Ive tried. How can I format this to correctly to add up all 4 quarters then do the division?
SELECT campus.campus,
ROUND(QTR1+QTR2+QTR3+QTR4/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;
CAMPUS % CONT
-- ----
Main 1300.16
East 700.08
West 300.04
North 350.04What do you mean by "closing the addition statement". Shouldn't you to put parenthesis over QTR1+QTR2+QTR3+QTR4 ?
SELECT campus.campus,
ROUND((QTR1+QTR2+QTR3+QTR4)/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;
I am having trouble getting the correct calculation with the statement below. The error is that QTR4 is being divided by Select SUM instead of all 4 quarters. I have tried closing the addition statements but get errors on all scenarios that Ive tried. How can I format this to correctly to add up all 4 quarters then do the division?
SELECT campus.campus,
ROUND(QTR1+QTR2+QTR3+QTR4/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;
CAMPUS % CONT
-- ----
Main 1300.16
East 700.08
West 300.04
North 350.04What do you mean by "closing the addition statement". Shouldn't you to put parenthesis over QTR1+QTR2+QTR3+QTR4 ?
SELECT campus.campus,
ROUND((QTR1+QTR2+QTR3+QTR4)/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;
Subscribe to:
Posts (Atom)