Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    11

    Unanswered: Sql query to pull certain row

    I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you know it's row 9054378701 ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    You can use the ROW_NUMBER function to give each row a number
    by using the table's index in the 'order by' clause, the rows will be ordered as they fall within the table (which should correspond to the row number you have.

    I too am curious how you know that row 9054378701 has the issue

    Just replace tblName with your table's name and tableID with the table's key.

    Code:
    WITH OrderedTable AS
    (
     SELECT *, ROW_NUMBER() OVER (Order by TableID) AS 'RowNumber'
     from tblName
    )
    select *
    from OrderedTable
    where rownumber = 9054378701

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by tmcrouse View Post
    I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
    What do you mean by "pull"? Delete that row?
    Code:
    DELETE 
    FROM MyTAble
    WHERE RowId = 9054378701
    But first have a look at what that data is in that record:
    Code:
    SELECT * 
    FROM MyTAble
    WHERE RowId = 9054378701
    When you've figuered out what column causes the runtime error, correct that data with correct information.
    Code:
    UPDATE MyTAble
    SET ColumnWithError = 'Corrected data'
    WHERE RowId = 9054378701
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •