About EasyMorph Tutorials & Examples Web-help

Identity_insert set to off error, while its not


#1

Hi,

I am migrating a database for a school project using easymorph with SQL server 2017.
I dont know if i am doing it wrong or if its something else, but everytime i try to use the Export to database transformation and try to insert it into a database table, it says that my identity_insert is set to OFF, while i checked and it clearly isn’t, to double check i turned identity_insert ON for every table in my database, but it still says my Identity_insert setting is set to OFF.

EDIT: I also don’t know if this problem has to do with easymorph or with SQL server 2017, I think its SQL server 2017, but since i double checked everything there i don’t know anymore.

If anyone would know how to fix this i’d like to know.

Thanks in advance


#2

Hi @Wessel_Ottevanger and welcome to the community,

are you trying to insert data into a table that has an identity column? Note that only one table in a session can have the IDENTITY_INSERT property set to ON (as per the docs).


#3

Yes i am trying to insert data into a table with an identity column, and i did not know that but that wont change the problem, i turned the Identity_insert ON on the table i want to use. but it wont work, no matter what i try


#4


This is basically what i did, and what happened, am i doing something wrong?


#5

Hi,

The problem here is that IDENTITY_INSERT is a session-level setting.
So you have to set it in the same session with INSERT statements,
which is not possible in the current version of EasyMorph.

But we are going to make some changes to Export to database transformation in order to make it possible.


#6

So right now i cant export my data to SQL server? or is there another way to do it?
Could i maybe delete my identity column and export it without, then when inserting generate a new one?
or is this not possible since my tuples need a primary key.


#7

Export can be executed without identity column - in such case news value will be generated for the identity column. But its highly possible that this workaround will mix up your identity values so I strongly suggest against using it.

You can try to use BULK INSERT command. This help page contains a short description on how to use it from EasyMorph (see chapter Bulk loading data into a database table).