Is there a way to programmatically import and export table data?

Question

Question

#InterSystems IRIS #InterSystems IRIS for Health

Are there any objectscript functions (not wizards) to export and then import table data (including RowID)?

It doesn't matter what format the exported file is.

The import and export process should preserve all relationships, foreign keys, etc. I need to be able to export and import multiple tables at the same time.

I need this to implement the ability to export multiple tables from one instance and load them into another.

For export I tried %SQL.StatementResult.DisplayFormatted with the CSV option, but it doesn't handle column names. The first line of the file is not csv for some reason. And it also doesn't handle values with commas inside.

For import, I tried "LOAD DATA", but it doesn't seem to be supported in my version of IRIS.

Product version: IRIS 2021.1

Discussion (3)1

Log in or sign up to continue

Timo Lindenschmid · Oct 23

Hi, without knowing a bit more about what you are trying to achieve its difficult to advise.

To export and reinstate a data structure including content you would need to export the storage class definition.

After that you then can export the Global data and index storage defined by the storage class.
To export you can use $system.OBJ.Export from a terminal/iris session. Then reimport using $system.OBJ.Load

0 0

score 1 · Answer 1 · 2024-10-23T03:07:11-04:00

If you're on that release I'm afraid you'll have to consider legacy options you may have found in the doc already: %SQL.Import.Mgr and %SQL.Export.Mgr. They lack comprehensive documentation, but from the example in their shared superclass, you can derive pretty much what you'd need.

I have not specifically tested my own SQL toolbox on 2021.1, but it doesn't rely on LOAD DATA for everything and may offer some suggestions on where to look next.

Something you'll likely need when trying to import row IDs from one instance to another is to temporarily override the InsertIdentity setting. See the INSERT reference doc for pointers on how to enable manually specifying RowID values.

score 0 · Answer 2 · 2024-10-24T09:51:22-04:00

Assuming all the classes are identical in both namespaces, then I think you can just copy over the underlying global arrays that hold the table data. You'll need to know a bit about the naming rules, etc. to find the globals, and a bit of programming to clear them out in the target beforehand. (The classes could be copied over separately.) If the namespaces are on the same network, and the tables small, then Merge commands could be used.