In software development, there’s a common scenario where we need to transport data between separated environments.

For instance, we routinely have to reproduce customer’s problems. In order to do that, we need the same data. Sometimes we just enter the production environment or request a backup, but in a few cases we aren’t allowed to do that! Some environments have security constraints that makes it impossible to obtain a backup, for example. We can request a query, but it isn’t proper because we want to work with the data.

How we can accomplish that?

Script that generates INSERTs

At first I developed an Oracle PL/SQL script to migrate data between different environments. It worked well so I did a SQL Server T-SQL version too.

Indeed, I’m talking about a procedure that generates INSERT commands from table data. Check out the following examples:

-- Generates INSERTS for all records from table TAB1
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB1', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = DEFAULT,
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for all records from table TAB2
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB2', -- table name
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for product table, but only when code is 'PROD' and company is 'COMP1'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'PRODUCT', -- table name
    @WHERE = 'ID = ''PROD'' AND ID_COMPANY = ''COMP1'' '

--Generates INSERTs for client table when client code is 123
EXEC GET_INSERT_SCRIPT
    @TABELA = 'CLIENT', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = 'ID = 123'

Remarks

In the first example, we set the source database as MY_DATABASE. This parameter is optional if the database is already selected with USE command.

The parameters with DEFAULT value instructs SQL Server to use the default value defined in procedure signature. They could be omitted.

The parameter @GERAR_DELETE defines if the generated script will have a DELETE command to remove the table data before the insertions.

The parameters @OWNER, @BANCO_ORIGEM, @BANCO_DESTINO can also be omitted and only need to be informed if you execute the script from master or from a different database owner. The default owner is dbo.

If these parameters are informed, the “path” to the database objects will be like this:

TARGET_DATABASE.dbo.TABLE

In the second example, the parameter @WHERE adds a filter clause to the generated data and DELETE command. This way we can load data selectively.

We only need be cautious with the output. Management Studio limits the text output size, so my advice is to change the configuration Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters... to 4000 (maximum characters per line) or just output to file.

Advantages

  • Store and migrate data in a clear and simple way.
  • Easily see and change all data.
  • Select exactly the data needed. Many tools don’t give you the option to export data selectively.
  • It ins’t necessary deploy any tool. The person who will generate the scripts just need to run a single script.

Potential problems

  • Tables with data types that cannot be represented as text cannot be generated.
  • Large tables will generate huge scripts hard to open in an editor.

Oracle version

The Oracle version haven’t all features I implemented in SQL Server, but as I told you in the beginning, it was successfully used to migrate data between different environments.

Available in

https://github.com/utluiz/database-insert-script-generator

Questions, suggestions, and bug fixes are welcome!