I am trying to copy a record in a SQL Server table that uses a Identity column. I would like to do the following:

INSERT INTO Mytable
SELECT * FROM MyTable WHERE Unqkey = 123456

However, the Identity column doesn't allow this. I was trying to take the record into a Foxpro Cursor via SQLEXEC and then create an insert statement to send back to SQL Server. I was attempting to create a FieldList and ValueList for an insert by scanning thru the SQLResult fields.

lcComma =""
FOR lnI = 1 TO lnFields
   lcFieldName = laFields(lnI,1)
   lcFieldType = laFields(lnI,2)
   IF UPPER(ALLTRIM(lcFieldName)) <> "UNQKEY"
      lcFieldList = lcFieldList + lcComma + '[' + lcFieldName + ']' + CHR(13) + CHR(10)
      lcValueList = lcValueList + lcComma + .r_oDataClass.m_Format_DBFField_For_SQL(lcFieldName,lcFieldType) + CHR(13) + CHR(10)
      lcComma = ","
   ENDIF
ENDFOR

I was attempting to create a SQL statement like

Insert into Mytable
(
lcFieldList
)
Values
(
lcValueList
)

However, the SQL Table has field names that FOXPRO doesn't like. There are field names that begin with Numeric characters. When I am scanning thru the fields, everything works fine until I hit the fields that begin with numeric characters and everything after that is NULL.

I was using a select * so I would not have to maintain this procedure for changes in the SQL Table. I could list out all the fields in a select and alias the bad field names, but then I would have to change this procedure any time the table changed.

Anybody got any good ideas on how the get the record copied easily and getting a new Identity value too? Thanks