Hi Jan,
sounds like a promising idea! I assume you use AML in your console app. Are you able to preserve the created_on/modified_on properties this way?
I have done some similar approach to duplicate a List item with AML. Most properties like "id" were preserved, but unfortunately not the ones related to created.../modified...
@Nathan: Regarding the SQL approach. Note that the following is just based on some test and could be wrong. The most basic query to transfer one database entry to another database looks like this. It requires that the database can access the second database (same server/linked server)
INSERT INTO [FirstDatabase].innovator.[USER] ([KEYED_NAME],[TEAM_ID],[CLASSIFICATION],[CELL],...)
SELECT [KEYED_NAME],[TEAM_ID],[CLASSIFICATION],[CELL],...
FROM [SecondDatabase].innovator.[USER] WHERE keyed_name = 'MyTestUser'
The above query will create a user entry, BUT not a valid user item, cause the Alias item is missing.
It´s important to add all properties in the query in correct order, otherwise there is the risk that certain properties will be mixed up. Happened to me, even if the two databases where identical and based on the same Innovator database.
The solution has a couple of pros&cons.
Pro #1: Speed and powerrr!!!
Con #1: It´s obviously super dangerous. AML protects your database and will prevent dangerous queries. SQL will not.
Pro #2: Data is copied 1:1
Con #2: Only one table at once can be filled with INSERT INTO. Regarding the List sample mentioned above, I would have to make two separate calls for all affected tables.
Pro #3: Data can be edited on the fly
Con #3: You really need to understand SQL and the data model of Innovator, otherwise you will destroy your db with a probability of 300% minimum .
Pro #4: Script can be prepared
Con #4: Can be used for plain data tables, but probably not for editing Innovator client itself (changes would have no effect).
But I haven´t done much testing yet. If somebody remembers old Top Gear episodes. INSERT INTO is equivalent to the "everything can be fixed with a hammer" approach of Clarkson. So I wouldn´t call it the perfect way to address the topic :D
Angela,
When I need to preserve properties like created_on/modified_on the only way is applySQL. However I use SQL strictly only for updating these properties. So I usualy do logic in folowing steps:
1. merge item with AML
2. get id of applied item
3. apply an SQL update statement just for these 2 properties
I believe that such a combination is the safest one.
Hi Jan,
thanks for the tip! :D
For my single ItemType mentioned above, your approach is indeed probably the safest. The ItemType does not use any versions, so I can overwrite the dates and states with SQL once the items exists.
But I definitely will be make further tests with the various available approaches we have. For update I will probably need a mix of all of them anyway. I just hope that I'll be able to finish before I retire.
Angela
