How to loop through a SQL column

Hi Guys

I have a table called TempMemo.
It consists of 2 columns: AccountId varchar(50)
                                        Memo text

I need to copy the data into a another table called SLMemo.

It consists of 2 columns: AccountId varchar(50)
                                        Memo varchar(1000)

The memo filled in Tempmemo will have more than 1000 characters.

I need all the data to come in.

So if a row in Memo in TempMemo has 5000 characters  then what should be imported:

5 lines of 1000 with the same Accountid.

Remebering the character len will be different.
I hope this makes sense.

Cheers
Shezad

Shezad AhmedAsked:
Who is Participating?
 
raulggonzalezCommented:
Hi,

The solution I've got is using a cursor, it's well known that cursors are not the best, but it works.

Hope it helps.


DECLARE @TempMemo 
	TABLE (AccountID VARCHAR(50),
			Memo TEXT)

DECLARE @SLMemo 
	TABLE (AccountID VARCHAR(50),
			Memo VARCHAR(1000))


INSERT INTO @TempMemo
VALUES(
'account1',
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'

)

INSERT INTO @TempMemo
VALUES(
'account2',
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456'

)


DECLARE @AccountID VARCHAR(50)
DECLARE @Memo VARCHAR(MAX) -- SQL 2005 doesn't allow TEXT variables, so I created VARCHAR(MAX)
DECLARE @Memo1000 VARCHAR(1000)
DECLARE @MemoLen INT
DECLARE @start INT  

SET @start = 1

DECLARE cr CURSOR
FORWARD_ONLY 
FOR
SELECT  AccountId , Memo text
FROM @TempMemo

OPEN cr
FETCH NEXT FROM cr INTO @AccountID, @Memo
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @MemoLen = LEN(@Memo)
   IF @MemoLen > 1000
   BEGIN
      WHILE @MemoLen > 0
      BEGIN
         SET @Memo1000 = SUBSTRING(@Memo, @start, 1000)
         INSERT INTO @SLMemo (AccountId ,Memo)
         VALUES (@AccountID, @Memo1000)
         
         SET @start = @start + 1000
         SET @MemoLen = @MemoLen - 1000
         
      END
   END
   
   ELSE
   BEGIN
      INSERT INTO @SLMemo (AccountId ,Memo)
      VALUES (@AccountID, @Memo)
    END

FETCH NEXT FROM cr INTO @AccountID, @Memo

END
CLOSE cr
DEALLOCATE cr

SELECT * FROM @SLMemo

Open in new window

0
 
rmm2001Commented:
There's probably better ways to do this but...
DECLARE @count INT
DECLARE @memoLen INT
DECLARE @memo TEXT
DECLARE @AccountID VARCHAR(50)
SET @count = (SELECT COUNT(9) FROM TempMemo)

WHILE (@count > 0)
  SELECT @AccountID = AccountID, @memo = Memo, @memoLen = LEN(Memo)
  FROM TempMemo
 
  WHILE (@MemoLen > 0)
    INSERT NewTable (AccountID, Memo)
    VALUES @AccountID, LEFT(Memo, 1000)
   
    SET @memoLen = @memoLen - 1000
  END
 
  SET @count = @count - 1
END  
0
 
raulggonzalezCommented:
Just realize that can do the same without the IF block...

Cheers
DECLARE @TempMemo 
	TABLE (AccountID VARCHAR(50),
			Memo TEXT)

DECLARE @SLMemo 
	TABLE (AccountID VARCHAR(50),
			Memo VARCHAR(1000))


INSERT INTO @TempMemo
VALUES(
'account1',
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'

)

INSERT INTO @TempMemo
VALUES(
'account2',
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456'

)


DECLARE @AccountID VARCHAR(50)
DECLARE @Memo VARCHAR(MAX) -- SQL 2005 doesn't allow TEXT variables, so I created VARCHAR(MAX)
DECLARE @Memo1000 VARCHAR(1000)
DECLARE @MemoLen INT
DECLARE @start INT  

SET @start = 1

DECLARE cr CURSOR
FORWARD_ONLY 
FOR
SELECT  AccountId , Memo text
FROM @TempMemo

OPEN cr
FETCH NEXT FROM cr INTO @AccountID, @Memo
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @MemoLen = LEN(@Memo)
      WHILE @MemoLen > 0
      BEGIN
         SET @Memo1000 = SUBSTRING(@Memo, @start, 1000)
         INSERT INTO @SLMemo (AccountId ,Memo)
         VALUES (@AccountID, @Memo1000)
         
         SET @start = @start + 1000
         SET @MemoLen = @MemoLen - 1000
         
      END
   
FETCH NEXT FROM cr INTO @AccountID, @Memo

END
CLOSE cr
DEALLOCATE cr

SELECT * FROM @SLMemo

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.