Hi Lynda:

Identity columns are system-generated to make sure they are properly auto-incremented (as well as unique, though an identity column does not identify a unique constraint). In that way, they are preferred to be ommitted in the columns list in the insert command.

However, the setting for IDENTITY_INSERT determines if you are allowed to populate values for identity fields in your insert statements. To enable inserts on identity columns, turn this setting ON. If you allow identiy inserts, chances are you will get duplicate values in your identity columns unless you include duplication checking before inserting.

I have not encountered problems with long SPTs. What I do for long statements is to store these statements into several variables and concatenate. For example...
lcSQLCmd1 = "select field1, field2, ..."
lcSQLCmd2 = " from table1 inner join table 2 on ..."
if sqlexec(lnConnectionHandle, lcSQLCmd1 + lcSQLCmd2, "Temp_cursor") < 1
&& error
endif

HTH,


>When using a SPT command like Insert into tableA Select * from tableB to add rows to a sql table, how do you deal with identity columns in one or both tables? Is the column_list required for one or both? If so, how do you keep the string length less than 255 characters? Concatenation seems to fail so there must be another way.

>SQL Books Online indicates an identity column may be omitted from the columns_list in the INSERT command, but doesn't really give any details or examples (surprise!)