• State Not Answered
  • +2 person also asked this people also asked this
  • Replies 9 replies
  • Subscribers 16 subscribers
  • Views 7774 views
  • Users 0 members are here
Options
Related

How to transfer table data from one Innovator instance to another fast and easy?

AngelaIp

Hi community,

I have two Innovator instances. Both have different purposes and unique applications.

I want to transfer one of the custom applications (incl. ItemType, Form, Method,..) AND the existing data from one instance to another.

The application itself is no big problem. We can create a PackageDefinition and use the existing Export/Import tool for transfer.
But what about the data?

In theory we could also create a PackageDefinition for the data. But the generated package do not contain all item properties. The exported items do not contain important properties like created_on/modified_on, permission_id, id, config_id, current_state, state, etc. .

But it´s important for me that the data is transferred with all of these properties.

How would you solve this task? There are many possible ways, but which one would you use?

My current ideas:

- SQL Insert select?
- CSV SQL export/import?
- AML export/import?
- transfer via IOM/REST connection?
- Innovator Admin?
- BatchLoader?
- Shell script?
- SQL script?
- Hire intern that type in the data manually?
- Something else?

I want to avoid using Excel or CSV as transfer medium. I want to use a concept that is reusable and maybe can be automated.
In addition I have to modify some of the data before import. These should be integrated into the process.

Any ideas and thoughts welcomed! Smiley

Thanks!

Angela

Parents
  • 0 Offline

    Hi,

    If this is a one time job and data model is complex you can try to create simple console app in c# using IOM with connection on both Aras instances.

    Do a get and merge logic and error handling. Than you can run in debug mode to catch errors and solve them on the fly.
    I have done similar cases in the past and it turned out to be a good choice.

    However any technology can do the job but at the and it depends on which technology you are most familiar with.

Reply
  • 0 Offline

    Hi,

    If this is a one time job and data model is complex you can try to create simple console app in c# using IOM with connection on both Aras instances.

    Do a get and merge logic and error handling. Than you can run in debug mode to catch errors and solve them on the fly.
    I have done similar cases in the past and it turned out to be a good choice.

    However any technology can do the job but at the and it depends on which technology you are most familiar with.

Children
  • 0 in reply to Jan Cvek

    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

    
			
    

		
		
		
		
	
    
	
    
		
    
			
		
    
	
    
	
    
		
						
						
				
						
				
				
		
		
    
			
		
    
	
    

    

    • 

	

  • 
	
    



	
		
	
	



    
	
    
		
    
							
					
    
		
    
			
													0
							
			
				Offline
									
							
						
				
			
												
						in reply to AngelaIp
					
									
    
	
    

		
		
    
					
    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.
    
			
    

		
		
		
		
	
    
	
    
		
    
			
		
    
	
    
	
    
		
						
						
				
						
				
				
		
		
    
			
		
    
	
    

    

    • 

	

  • 
	
    



	
		
	
	



    
	
    
		
    
							
					
    
		
    
			
													0
							
			
				
									
							
						
				
			
												
						in reply to Jan Cvek
					
									
    
	
    

		
		
    
					
    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
    
			
    

		
		
		
		
	
    
	
    
		
    
			
		
    
	
    
	
    
		
						
						
				
						
				
				
		
		
    
			
		
    
	
    

    

    •