Sql query to pull certain row

tmcrouse · #1 (**permalink**) 02-07-12, 14:13

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.

r937 · #2 (**permalink**) 02-07-12, 15:23

how do you know it's row 9054378701 ??

Mark_Otmarich · #3 (**permalink**) 02-07-12, 17:09

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

Wim · #4 (**permalink**) 02-08-12, 04:44

Quote:

Originally Posted by tmcrouse

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

Internet Services

Web Development

Digital Marketing

Consumer Tech