I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
1) select X number of rows from table1
2) I need colums for each row of table1 in a new table
3) As such, Select (select * from X where x.id = @.ID), a,b,c from table Y where y.Id = @.ID
And I dont want to use IfExists.
Thanks
DK
I don't think it is possible the way your are describing.
You could try the pivot method described in this article:
http://dotnet.sys-con.com/read/45543.htm
This one works:
SELECT
OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumberFROM
ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15
Syntax in SQL Server 2005:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
sqlWithout going into a lot of history, I am using embedded SQL in a Micro-Focus
COBOL application.
I have a need to be able to select a specific row from my table and lock that row until I release it.
I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately
it also locks the entire table.
I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation
and all I want is a clear answer as to how this should work when I have to do it.
here is a sample of what I have tried. I'm hoping someone has successfully done something
like this (maybe not even in cobol).
EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.
EXEC SQL
OPEN TECHLK
END-EXEC.
EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.
any help would be appreciated.. thx, Ray Dodson
1. How did you identify that entire table is locked?
2. What queries does your application actually sent to the server? You can figure that out using Profile tool.
3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.
|||This reply asked more questions than it answered.
1. I used the management item in seql server enterprise manager to see the
locking status.
Actually, I found after this post was sent that my keys were not set up correctly (imported
from another database). that appears to be why I was locking the entire table. I have rectified that
and things seem to be working correctly now.
thanks for you time... Ray Dodson
i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.
SP Code
ALTER
PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]@.assignmentPrimaryKey
int,@.rowCount
intOUTPUTAS
BEGIN
SETNOCOUNTON;SELECT employeePrimaryKeyFROM assignmentTableWHERE primaryKey= @.assignmentPrimaryKey;SET @.rowCount=@.@.RowCount;END
c# code
SqlConnection
conn = GetOpenSqlConnection();if (conn ==null) returntrue;SqlDataReader reader =null;SqlParameter p1 =newSqlParameter(); SqlParameter p2 =newSqlParameter();try{SqlCommand command =newSqlCommand();command.CommandText =
"RetrieveEmployeeKeyFromAssignmentTable";command.CommandType =
CommandType.StoredProcedure;command.Connection = conn;
p1.ParameterName =
"@.assignmentPrimaryKey";p1.Value = assignmentPrimaryKey;
p2.ParameterName =
"@.rowCount";p2.Direction =
ParameterDirection.Output;p2.Value = 0;
command.Parameters.Add(p1); command.Parameters.Add(p2);reader = command.ExecuteReader();
if (p2.Value ==null)//always trueany suggestions would be appreciated.
thanks. matt
also, reader.HasRows is true.
matt
|||Hello,
first, I was suspicious of your code that you assigned the value of output parameter p2.value=0.
but it was O.K., and I was wrong , I tested with query analyzer.
I looked carefully in your code, find that you called with ExecuteReader ==> it is connected data object.
you have to close the Reader object before you try to get Output or else paremeter values.
verified with internet search...
|||
EXCELLENT.
thank you very much.
matt
Is there a way to insert the rowcount in a 'select into' clause? Example:
select @.@.rowcount as id,* into mytable_with_rowcount from mytable
If mytable has 100 records then I want an id column in 'mytable_with_rowcount' and have the id column numbered 1 - 100. I know this can be done in a loop, but can it be done in the select w/out doing a loop?
Thanks,
Phil
if its sql server 2005 you can use Ranking Function to number the row and then insert to table. Read about Ranking Functions in BOL.
If its 2000, then you can create a table with ID as indentity column and then insert the rows to that table.
http://www.sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://www.sql-server-performance.com/ak_ranking_functions.asp
Madhu
|||Very nice. Thanks.Can this function accept parameter in the order clause?
WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)
Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.
Thanks,
You could use dynamic sql (execute a string using EXEC or sp_executesql).|||ThanksCan this function accept parameter in the order clause?
WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)
Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.
Thanks,
There are several options:
1. Use dynamic SQL to generate the entire SELECT statement
2. Use CASE expression in the ORDER BY clause like:
ORDER BY case @.SortColumn when 1 then col1 end,
case @.SortColumn when 2 then col2 end
3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.
There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.
|||Thank you so muchIs there a way without rerunning the select query to get the "total row count" using
ROW_NUMBER and BETWEEN as such..
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum,
e.id, e.Title
FROM Events e) as DerivedTableName
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1
typically i would build a temp table and return SELECT @.@.ROWCOUNT
? Why not just do: SELECT COUNT(*) FROM Events e -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:f54208af-7f15-440a-b9fa-41e0f8c59ada@.discussions.microsoft.com... Is there a way without rerunning the select query to get the "total row count" using ROW_NUMBER and BETWEEN as such.. SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Year DESC, Month DESC, Day DESC) as RowNum, e.id, e.Title FROM Events e ) as DerivedTableName WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1 typically i would build a temp table and return SELECT @.@.ROWCOUNT|||yeah that is pretty much what i'm left with right now but, that was an overly simplified example.
i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind.
knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.
|||? Yes, I believe that the ROW_NUMBER solution is far superior to the temp table. I also think that running the query twice is not a huge issue if your users page past the first page on most searches. In that case, it means a better user experience. But if that's not the case, I might think twice about taking the full count if I were you. When it comes to paging, it's really a choice of performance vs. a slightly less functional UI -- and in many cases the full count really doesn't add that much anyway. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <jkgreer@.discussions.microsoft.com> wrote in message news:ea3176ea-5d77-4399-a575-31284186b42e_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com yeah that is pretty much what i'm left with right now but, that was an overly simplified example. i'm feeding very complex where and order statements into it and if there isn't any overhead in rerunning the query i wouldn't mind. knowing the virtual count of rows / pages seems to go hand in hand with paging and i wanted to make sure that switching to using ROW_NUMBER() and BETWEEN was a better choice than creating a temp table of ordered indexes and joining off of it just to retrieve the full count of filtered rows.|||Thanks. that is what i expected i suppose.
I was going for the full featured with this question, but i have no issue letting users fall off the end of a page in places, point well taken.
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
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.