Problem Importing data with imbedded single quotes.

[LeonAtRC]

Here is the actual code I'm running to copy data from one set of tables to another. The table structures are identical.

SET @runCmd = 'INSERT INTO dbo.' + @dbTable + ' (' + @fldList + ') SELECT ' + @fldList + ' FROM dbo.' + @tmpTable
EXECUTE (@runCmd)

@dbTable is the new table
@fldList is the list of fields
@tmpTable is the old table.

I'm getting errors because of apostophes in the data.
This is the code used to build the field list:

SET @fldList = ''
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @fldList = @fldList +'['+ @colName+']' + ','
		FETCH NEXT FROM cols INTO @colName
	END
SET @fldList = LEFT(@fldList,LEN(@fldList)-1)

 

[gmmastros]

To accommodate single quotes, you need to double them. This means you'll need to create 2 separate field lists.

SET @ColumnList = ''
Set @DataList = ''

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @ColumnList = @ColumnList +'['+ @colName+']' + ','
        SET @DataList = @DataList + 'Replace([' + @colName + '], '''''',''''),'
        FETCH NEXT FROM cols INTO @colName
    END
SET @ColumnList = LEFT(@ColumnList ,LEN(@ColumnList )-1)
SET @DataList = LEFT(@DataList ,LEN(@DataList )-1)

And then....

SET @runCmd = 'INSERT INTO dbo.' + @dbTable + ' (' + @ColumnList + ') SELECT ' + @DataList + ' FROM dbo.' + @tmpTable
EXECUTE (@runCmd)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom