In our application for holding databases I am trying to get an SQL command working for copying one table to another - including the other tables id as the new id for the current table.
I made a new entity, put just a text attribute and a reference attribute and the below works fine - no error thrown and I even copied over items to the new entity and it worked as it should.
However, then I try to do the same thing on one of my existing tables (with alot of attributes and references and indexes) and just trying to do the set on and off I get the following error. I am not sure what it means by its not a user table. Anyone run into this before or have any ideas?
sql:
SET IDENTITY_INSERT {Tool} ON;
/*INSERT INTO {TestingAutoIdTable_CopyTo} ({TestingAutoIdTable}.[Id],{TestingAutoIdTable}.[Text]) SELECT top 5 {Tool}.[Id],{Tool}.[Name] FROM {Tool};*/
SET IDENTITY_INSERT {Tool} OFF;
The error returned when I try to use this on my existing tool entity (not the new test one I just made):
Database returned the following error: Error in advanced query SQL1: 'OUTSYSTEMS.dbo.OSUSR_GUX_TOOL_T20' is not a user table. Cannot perform SET operation.
So I think I found the fix for this - apparently if your entity is mult-tenant and you have "Show tenant id" not checked, you are not actually looking at the table - instead you are looking at a view of the table. If you set "Show tenant id" to true and upload and then try the above sql - it works.
Hi,
I guess you cannot insert into an Identity column explicitly if the Autonumber property is set to true for the entity Identifier attribute.
Regards,
Daniel