SET NOCOUNT ON

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'WareGroup'
    WHERE SysObjects.Name = 'Color'
)
  ALTER TABLE Color ADD WareGroup BIT NULL 
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'WareGroupText'
    WHERE SysObjects.Name = 'Color'
)
  ALTER TABLE Color ADD WareGroupText VARCHAR(50) NULL 
GO

CREATE TABLE #tmpColor
(
  Color INT,
  Red INT,
  Green INT,
  Blue INT
)

INSERT INTO #tmpColor (Color) VALUES (16764108)
INSERT INTO #tmpColor (Color) VALUES (13421823)
INSERT INTO #tmpColor (Color) VALUES (13428428)
INSERT INTO #tmpColor (Color) VALUES (13434879)
INSERT INTO #tmpColor (Color) VALUES (14810997)
INSERT INTO #tmpColor (Color) VALUES (13631457)
INSERT INTO #tmpColor (Color) VALUES (12632256)
INSERT INTO #tmpColor (Color) VALUES (7901695)
INSERT INTO #tmpColor (Color) VALUES (255)
INSERT INTO #tmpColor (Color) VALUES (1555967)
INSERT INTO #tmpColor (Color) VALUES (16711680)
INSERT INTO #tmpColor (Color) VALUES (32768)

UPDATE #tmpColor
SET Red = ((Color % (256*256)) % 256), 
Green = (Color % (256*256))/256,
Blue = Color/(256*256)

DECLARE @Color INT
DECLARE @Red INT
DECLARE @Green INT
DECLARE @Blue INT
DECLARE @ColorRGBID INT
DECLARE @Ident INT

DECLARE #dbCur CURSOR FOR
  SELECT Color, Red, Green, Blue, 
  (
    SELECT TOP 1 ColorRGBID
    FROM ColorRGB WITH (NOLOCK)
    WHERE ColorRGB.Red = #tmpColor.Red
    AND ColorRGB.Green = #tmpColor.Green
    AND ColorRGB.Blue = #tmpColor.Blue
  )
  FROM #tmpColor
OPEN #dbCur
FETCH NEXT FROM #dbCur INTO @Color, @Red, @Green, @Blue, @ColorRGBID

WHILE @@FETCH_STATUS = 0
BEGIN
  IF ISNULL(@ColorRGBID, 0) > 0
    UPDATE Color SET WareGroup = 1 WHERE FK_ColorRGBID = @ColorRGBID
  ELSE
  BEGIN
    INSERT INTO ColorRGB (Red, Green, Blue) VALUES (@Red, @Green, @Blue)
    SELECT @Ident = SCOPE_IDENTITY()
    INSERT INTO Color (ColorName, AutoDayColor, SortOrder, SetupSortOrder, YieldFilterColor, FK_ColorRGBID, YieldFilterText, WareGroup, WareGroupText) 
      VALUES ('UNKNOWN', 0, 999, 999, 0, @Ident, '', 1, '')
  END

  FETCH NEXT FROM #dbCur INTO @Color, @Red, @Green, @Blue, @ColorRGBID
END

CLOSE #dbCur
DEALLOCATE #dbCur

DROP TABLE #tmpColor