Web Listing 3:  Code to Transfer Data 

if exists (select * from dbo.sysobjects where id = object_id('dbo._TEMP_Keywords_Catalog') 
	and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE dbo._TEMP_Keywords_Catalog
GO
if exists (select * from dbo.sysobjects where id = object_id('dbo._TEMP_KeywordArray') 
	and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE dbo._TEMP_KeywordArray
GO
if exists (select * from dbo.sysobjects where id = object_id('dbo._keys') 
	and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE dbo._keys
GO
if exists (select * from dbo.sysobjects where id = object_id('dbo._keys2') 
	and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE dbo._keys2
GO
/*-------------------------------------------------------------------
	Transfer data into dbo.Image.
  -------------------------------------------------------------------*/
-- Where's the data coming from?	/* Callout A */
select * from dbo.ASSET			-- (33 row(s) affected)  /* Callout B */
--
-- Load dbo.IMAGE.
--
begin tran
SET IDENTITY_INSERT dbo.IMAGE ON	/* Callout C */
INSERT INTO dbo.IMAGE(
	ImageID, ImageName, ImageFile
)
SELECT ID, name, asset_image
FROM dbo.ASSET					-- (33 row(s) affected)
SET IDENTITY_INSERT dbo.IMAGE OFF
commit

select * from dbo.IMAGE			-- (33 row(s) affected)


/*-------------------------------------------------------------------
	Transfer data into dbo.Keyword.		/* Callout D */
  -------------------------------------------------------------------*/
-- Where's the data coming from?
select * from dbo.ASSET			-- (33 row(s) affected)
--
-- What's the longest keyword array?
--
SELECT max(len(keywords)) FROM dbo.ASSET	-- 40 bytes	/* Callout E */

---------------------------------------------------------------------------------------------
-- The IDs and Keywords will end up in this table before being loaded into the Keywords table.
---------------------------------------------------------------------------------------------
CREATE TABLE _TEMP_Keywords_Catalog		/* Callout F */
 ( ID Int, Keywords varchar (50))
-----------------------------------------------
-- Holding tablefor the cursor. 
-----------------------------------------------
CREATE TABLE _TEMP_KeywordArray		/* Callout G */
 (ID INT,
  Keywords Varchar (50))
GO
--
-- Copy keyword strings into temp table,
-- truncating spaces on the right. 
INSERT INTO _TEMP_KeywordArray
SELECT ID, RTRIM (keywords)
from dbo.asset
WHERE keywords <> ' '		-- (33 row(s) affected)
GO					
-------------------------
-- Start iteration here.
-------------------------
SET NOCOUNT ON
GO
DECLARE @ID varchar (10)
DECLARE @keywords varchar (200)

DECLARE @SPACE int 		-- Looking for position of first space
DECLARE @SPACEM1 int 		-- Length of keyword
DECLARE @KeywordLength int	-- total length of input
DECLARE @Remainder int		-- Remainder of the string that's just been truncated

DECLARE keywords_cursor CURSOR FOR 
SELECT ID, Keywords 
FROM _TEMP_KeywordArray
ORDER BY ID

OPEN keywords_cursor
FETCH NEXT FROM keywords_cursor
INTO @ID, @keywords

WHILE @@FETCH_STATUS = 0

BEGIN
KEYWORD_LOOP:

  BEGIN

  SET @SPACE = 0
  SET @SPACEM1 = 0
  SET @KeywordLength = 0
  SET @Remainder = 0
----------------------------------
-- Look for spaces in the string.	/* Callout H */
----------------------------------
  SET @SPACE =  CHARINDEX(' ', @keywords, 1)
  SET @SPACEM1 = @SPACE - 1
  SET @KeywordLength =  LEN (@keywords)
  SET @Remainder = @KeywordLength - @SPACE

/** If there's a space, put the ID and everything up to the space
    in the holding table, then lop off everything up to and including 
    the space in this table. Trim off any leading spaces in case there
    are multiple spaces.
    If there is no space, then there is only one keyword in the 
    column. Put that into the holding table and move on to the
    next line.
**/

  IF @space <> 0
    BEGIN
      INSERT INTO _TEMP_Keywords_Catalog
      SELECT @ID, LEFT (@keywords, @SPACEM1)
      SET @keywords = RIGHT (@Keywords, @Remainder)
      SET @keywords = LTRIM (@keywords)
      GOTO KEYWORD_LOOP
    END
  ELSE
    BEGIN
      INSERT INTO _TEMP_Keywords_Catalog
      SELECT @ID, @keywords
    END
  END

  FETCH NEXT FROM keywords_cursor INTO @ID, @keywords
  END

  CLOSE keywords_cursor
  DEALLOCATE keywords_cursor
  SET NOCOUNT OFF
  GO
-- Check the output; optional.
select * from _TEMP_Keywords_Catalog			-- (109 row(s) affected)

--------------------------------------------
--Take care of words that have commas at the end. 		/* Callout I */
--------------------------------------------
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-1))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,1)  Like ','			-- (3 row(s) affected)
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,1)  Like ','			-- (3 row(s) affected)
GO
-- Check the output; optional.
select * from _TEMP_Keywords_Catalog			-- (109 row(s) affected)

----------------------------------------------------------------------
-- If words have carriage return plus line feed at the end,
-- remove them. Some lines may have multiple CR+LF.
----------------------------------------------------------------------
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-8))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,8)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10) + CHAR (13)+ 
char (10) + CHAR (13)+ char (10)
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,8)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10) + CHAR (13)+ 
char (10) + CHAR (13)+ char (10)
GO
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-6))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,6)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10) + CHAR (13)+ 
char (10)
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,6)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10) + CHAR (13)+ 
char (10)
GO
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-4))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,4)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10)
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,4)  Like CHAR (13)+ char (10) + CHAR (13)+ char (10)
GO
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-2))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,2)  Like CHAR (13)+ char (10) 
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,2)  Like CHAR (13)+ char (10) 
GO
------------------------------------------------------------------
-- Some words might have embedded Carriage return plus line feed. 
-- (This should not be possible but does happen.)
------------------------------------------------------------------
DELETE FROM _TEMP_Keywords_Catalog
where keywords like '%' + CHAR (13)+ char (10) + '%'
GO
--------------------------------------------
-- Take care of lines with a tab at the end.
--------------------------------------------
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-2))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,2)  Like '%' +CHAR (9) + CHAR (9)
GO
INSERT INTO _TEMP_Keywords_Catalog
SELECT ID, LEFT (Keywords, (LEN(keywords)-1))
FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,1)  Like '%' + CHAR (9)
GO
DELETE FROM _TEMP_Keywords_Catalog
WHERE RIGHT (keywords,1)  Like CHAR (9)
GO
DELETE FROM _TEMP_Keywords_Catalog
where keywords = ' '
GO
DELETE FROM _TEMP_Keywords_Catalog
where keywords IS NULL
GO

------------------------------------
-- Load the production table.
------------------------------------
begin tran
INSERT INTO dbo.KEYWORD(KeywordDscr)				/* Callout J */
SELECT DISTINCT Keywords FROM _TEMP_Keywords_Catalog		-- Don't load 
duplicates.
WHERE Keywords NOT IN (SELECT KeywordDscr FROM dbo.KEYWORD)	
ORDER BY keywords						-- (67 row(s) affected)
--
-- Check the output (optional).
 select * from dbo.KEYWORD					-- (67 row(s) affected)
 select * from _TEMP_Keywords_Catalog				-- (109 row(s) affected)
 select distinct keywords from _TEMP_Keywords_Catalog	-- (67 row(s) affected)
--
commit
--------------------------------------------
-- And also load the original string value.			/* Callout K */
--------------------------------------------
begin tran
INSERT INTO dbo.KEYWORD(KeywordDscr)
SELECT DISTINCT Keywords FROM _TEMP_KeywordArray		-- Don't load duplicates.
WHERE Keywords NOT IN (SELECT KeywordDscr FROM Keyword)		
ORDER BY keywords						-- (30 row(s) affected)
--
-- Check the output (optional).
 select * from dbo.KEYWORD					-- (97 row(s) affected)
 select * from _TEMP_Keywords_Catalog				-- (109 row(s) affected)
 select distinct keywords from _TEMP_Keywords_Catalog	-- (67 row(s) affected)
--
commit
-----------------
-- Check result.
-----------------
select * from dbo.KEYWORD		-- (97 row(s) affected)


/*-------------------------------------------------------------------
	Transfer data into dbo.ImageKeyword.					/* Callout L */
  -------------------------------------------------------------------*/
-- Where's the data coming from?
select * from _TEMP_Keywords_Catalog order by 1	-- (109 row(s) affected)
select * from _TEMP_KeywordArray	order by 1	-- (33 row(s) affected)
--
-- Combine the content of _TEMP_Keywords_Catalog and _TEMP_KeywordArray
--   into a temp table (get original array and component words).
SELECT ID, Keywords
INTO _Keys
FROM _TEMP_KeywordArray			-- (33 row(s) affected)
--
INSERT INTO _Keys
SELECT ID, Keywords
FROM _TEMP_Keywords_Catalog		-- (109 row(s) affected)
-- 
 select * from _Keys order by 1,2	-- (142 row(s) affected)
-- 
-----------------------------------------------
-- Look for "blank" keywords, and remove them.			/* Callout M */
-----------------------------------------------
select * from _Keys 
where keywords IS NULL
   or keywords like ''
   or keywords like CHAR (13)+ char (10) + '%'
order by 1,2				-- (0 row(s) affected)
--
begin tran
DELETE from _Keys 
where keywords IS NULL
   or keywords like ''
   or keywords like CHAR (13)+ char (10) + '%'
commit
--
----------------------------------
-- Are there duplicate records?					/* Callout N */
----------------------------------
select ID, keywords from _Keys			-- (142 row(s) affected)
select distinct ID, keywords from _Keys		-- (141 row(s) affected)
--
-- Filter out the duplicates.
select distinct ID, Keywords 
INTO _Keys2
from _Keys			-- (141 row(s) affected)
-- 
select * from dbo.IMAGE		-- (33 row(s) affected)
select * from dbo.KEYWORD	-- (97 row(s) affected)

-- Test the code.
SELECT i.ImageID, i.ImageName, i.ImageFile, k.KeywordDscr
FROM dbo.IMAGE i 
	JOIN  _Keys2 k2 ON i.ImageID = k2.ID			-- (141 row(s) affected)
	JOIN dbo.KEYWORD k ON k2.keywords = k.KeywordDscr	-- (141 row(s) affected)
order by 1, 2
------------------------------------
-- Load the associative table.
------------------------------------
begin tran
INSERT INTO SQLmag.dbo.IMAGEKEYWORD(
	ImageID, KeywordID
)
SELECT i.ImageID, k.KeywordID
FROM dbo.IMAGE i 
	JOIN  _Keys2 k2 ON i.ImageID = k2.ID			
	JOIN dbo.KEYWORD k ON k2.keywords = k.KeywordDscr	-- (141 row(s) affected)
commit

------------------------------------
-- Final check
------------------------------------
SELECT *
FROM dbo.IMAGE i 						/* Callout O */
	JOIN dbo.IMAGEKEYWORD ik ON i.ImageID = ik.ImageID	-- (141 row(s) affected)
	JOIN dbo.KEYWORD k ON ik.KeywordID = k.KeywordID		-- (141 row(s) affected)
order by 1, 2

/*------------------------------
	Cleanup							/* Callout P */
  ----------------------------*/
drop table _TEMP_Keywords_Catalog
drop table _TEMP_KeywordArray
drop table _Keys
drop table _Keys2