T-SQL Script to Loop Within Table Rows and Loop Within String in Each Row

This SQL script is for Microsoft SQL 2008 and it may just work with older version Microsoft SQL. This script can also be used within a DocuWare application where a user wants to move data from a regular text column to a keyword field, which is a separate table in DocuWare. The text field data has to be separated by a unique delimiter (in this case I am using commas).

Let me give you an image of table that am trying to manipulate:

I wanted to create a separate table or move data from this main table to another table for “STATEZIPCODES” column. The main goal is to have a table with state-names and zip-codes and each state in a row with its corresponding zip-codes. Similar to what in database terms is called data normalization.

Here is a view of what the output looks like:

Here is the T-SQL script:

SET NOCOUNT ON
/*
-----------------------------------------------------------------------------
www.myw0.com, Version 1.0, Creation Date: 12/15/2010
Functionality:
This SQL script will loop within a table using cursor and then loop
within a string to get all values from a string to build or migrate
the data to another table. In this scenario I am moving the values
to a temporary table.
-----------------------------------------------------------------------------
*/
-- Variables
DECLARE @COLUMNDBSTRING AS VARCHAR(255),
@TMPSTRING AS VARCHAR(255),
@DOCUMENT_ID AS VARCHAR(50),
@Cnt AS int,
@Pos int
-- Temp Table
CREATE TABLE #DATAINSERTER (documentID VARCHAR(50), data_string VARCHAR(255))
-- Crate the first loop to find how many rows exist or have data on it
-- Declare a SQL cursor for to go to those selected databases
DECLARE TableCursor CURSOR fast_forward for
select [STATENAME], [STATEZIPCODES] from [dwdata].[dbo].[VENDORS] WHERE [STATENAME] IS NOT NULL
--AND DWDOCID = 35
open TableCursor
fetch next from TableCursor
into @DOCUMENT_ID, @COLUMNDBSTRING
while @@fetch_status = 0
begin
set @Cnt = 0
-- Get the position of the first comma
set @Pos = PATINDEX('%[,]%', @COLUMNDBSTRING)
-- Start the look until the the positionis equal to zero
while @Pos > 0
begin
set @Cnt = @Cnt + 1
-- Get Values from the string
SELECT @TMPSTRING = SUBSTRING(@COLUMNDBSTRING, 0, @Pos)
-- Insert that value into a temporary table
INSERT INTO #DATAINSERTER (documentID, data_string) VALUES (LTRIM(RTRIM(@DOCUMENT_ID)), LTRIM(RTRIM(@TMPSTRING)))
--Delete the string that was taken
set @COLUMNDBSTRING = SUBSTRING(@COLUMNDBSTRING, @Pos + 1, 255)
-- Reset the position for position counter
set @Pos = PATINDEX('%[,]%', @COLUMNDBSTRING)
end
-- If the position is equal to zero, no more commas ... just get the value from long string
if @Pos = 0
begin
-- Get Values from the long string
INSERT INTO #DATAINSERTER (documentID, data_string) VALUES (LTRIM(RTRIM(@DOCUMENT_ID)), LTRIM(RTRIM(@COLUMNDBSTRING)))
end
-- Get the next rown in the table
fetch next from TableCursor
into @DOCUMENT_ID, @COLUMNDBSTRING
end
close TableCursor
deallocate TableCursor
SET NOCOUNT OFF
-- Testing the script
select * from #DATAINSERTER ORDER BY documentID
-- Delete the temp table
DROP TABLE #DATAINSERTER