Reading data from table line by line

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reading data from table line by line

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-11-28 : 16:33:13

How can I read the data from the table line by line and display it without making the use of CURSORS

Here is my current code:

DECLARE @Text varchar(8000)
DECLARE @TotalRows int
DECLARE @Counter int

SELECT @Text = line FROM #TextFile

SET @Counter = 0
SET @TotalRows = SELECT COUNT(line) FROM #TextFile

WHILE @Counter <= @TotalRows
BEGIN
SELECT @Text = line FROM #TextFile -- error here
PRINT @Text
SET @Counter = @Counter + 1

END

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-28 : 16:57:17

You've already posted the code that doesn't use cursors. The only way is through a cursor or a WHILE loop. Either way you go, it isn't going to be efficient.

Tara Kizer
aka tduggan

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-11-28 : 17:20:57

Thanks for the reply: In the above code there is an error,

DECLARE @Text varchar(8000)
DECLARE @TotalRows int
DECLARE @Counter int

SELECT @Text = line FROM #TextFile

SET @Counter = 0
SET @TotalRows = SELECT COUNT(line) FROM #TextFile

WHILE @Counter <= @TotalRows
BEGIN
SELECT @Text = line FROM #TextFile -- ERROR HERE
PRINT @Text
SET @Counter = @Counter + 1
END

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-28 : 17:26:49

Try this:


DECLARE @Text varchar(8000)
DECLARE @TotalRows int
DECLARE @Counter int

SELECT @Text = line 
FROM #TextFile

SET @Counter = 0

SELECT @TotalRows = COUNT(line) 
FROM #TextFile

WHILE @Counter <= @TotalRows
BEGIN
	SELECT @Text = line
	FROM #TextFile 
	
	PRINT @Text

	SET @Counter = @Counter + 1
END

Tara Kizer
aka tduggan

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-11-28 : 17:31:23

Hi Tara,

Thanks. Your query ran. But it keeps giving me the last line in the Table. How can I print each line. I have acheived this using CURSORS but I am not sure how much performance hit CURSORS will get.

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-28 : 17:40:49

You need to have a column in your #TextFile table that allows you to determine which line you are on. A simple way is to add an identity column.

Using a WHILE loop is not going to be any more efficient than a cursor. Both will be inefficient.


SET NOCOUNT ON

DECLARE @Text varchar(8000)
DECLARE @TotalRows int
DECLARE @Counter int

CREATE TABLE #TextFile (TextFile_ID int IDENTITY (1, 1), line varchar(8000))
INSERT INTO #TextFile (line) VALUES('1')
INSERT INTO #TextFile (line) VALUES('2')
INSERT INTO #TextFile (line) VALUES('3')

SET @Counter = 1

SELECT @TotalRows = MAX(TextFile_ID) 
FROM #TextFile

WHILE @Counter <= @TotalRows
BEGIN
	SELECT @Text = line 
	FROM #TextFile
	WHERE TextFile_ID = @Counter
	
	PRINT @Text

	SET @Counter = @Counter + 1
END

DROP TABLE #TextFile

Tara Kizer
aka tduggan

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-11-28 : 17:44:20

Sweet Deal! I mean cool you are GODDESS!!!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources