Introduction
The restore process which is focusing only into the execution of an insert statement ended in a failure. Actually, there is an SQL file containing hundreds of records or rows available as values for insert query in its associated columns. But the process for importing those records or row data fail because of a certain cause. The following is the execution of the process for importing records or row data from an SQL file with the name of ‘insert-active-employee.sql’ :
C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-active-employee.sql" Password for user db_user: ERROR: cross-database references are not implemented: "main.dbo.employee" LINE 1: INSERT INTO main.dbo.employee (name,birthdate,address... ^
Solution
Actually, the solution for solving the above problem is very simple. It exist in the name of the database which is being the target for the import process. There is no cross-reference database in this context. The SQL file actually exist as the process from Microsoft SQL Server backup or SQL insert statement generated process. In other words, the SQL file source is from Microsoft SQL Server. But the target for the restore process is not a Microsoft SQL Server. Instead, it is a PostgreSQL database server as the target of the database. The solution is very simple, just replace the cross-database references above with another suitable format.
So, check the database PostgreSQL name and then look up for the table. Actually in the command for restoring or importing the records or the row data, the database name is already becoming part of the value from one of the argument. The argument exist in ‘-d db_app’ where the database name is ‘db_app.
The only part left is to edit the SQL file further. Just replace the cross-database references exist in the above which is ‘main.dbo.employee’ into a name of a table from the database ‘db_app’. In this context as an example it is ”. The following is the pattern of the INSERT statement available in the SQL file before the editing process :
INSERT INTO main.dbo.employee (name,birthdate,address... VALUES(...,,,)
Following after, below is the actual content of the SQL file after the editing process :
INSERT INTO public.org_employee (name,birthdate,address... VALUES(...,,,)
Since, PostgreSQL has a default schema of ‘public’, so the definition of the table will have a reference of ‘public.org_employee’ where ‘org_employee’ is the name of the table itself. After editing it, just execute it once more and the INSERT query process will be proceed normally if there are no more errors exist.