matthieusb matthieusb - 5 months ago 43
SQL Question

Jetbrains Datagrip 2017.1.3, force columns exported when dumping data to sql inserts file

I have an SQL server database with a lot of tables and data. I need to reproduce it locally in a docker container.
I have successfully exported the schema and reproduced it. When I dump data to an SQL file, it does not export automatically generated fields (Like ids or uuids for example)

Here is the schema for the user table:

create table user (
id_user bigint identity constraint PK_user primary key,
uuid uniqueidentifier default newsequentialid() not null,
id_salarie bigint constraint FK_user_salarie references salarie,
date_creation datetime,
login nvarchar(100)

When it exports and element from this table, I get this kind of insert:

INSERT INTO user(id_salarie, date_creation, login) VALUES (1, null, "example")

As a consequence, most of my inserts give me foreign key errors, because the ids generated by my new database are not the same as the ones in the old database. I can't change everything manually as there is way too much data.

Instead, I would like to have this kind of insert:

INSERT INTO user(id_user, uuid, id_salarie, date_creation, login) VALUES (1, 1, "manuallyentereduuid" null, "example")

Is there any way to do this with Datagrid directly? Or maybe a specific SQL server way of generating insert statements this way?

Don't hesitate to ask for more details in comments.

Answer Source

It seems like Datagrip does not give you that possibility so I used something else : DBeaver. It is free and based on the Eclipse Environment.

The method is simple :

  • Select all the tables you want to export
  • Right click -> Export table data

From there you just have to follow the instructions. It outputs one file per table, which is a good thing if you have a consequent volumetry. I had trouble executing the whole script and had to split it when using Datagrip.

Hope this helps anyone encountering the same problem. If you find the solution directly in datagrip, I would like to know too.