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'
In the first example, we set the source database as
MY_DATABASE. This parameter is optional if the database is already selected with
The parameters with
DEFAULT value instructs SQL Server to use the default value defined in procedure signature. They could be omitted.
@GERAR_DELETE defines if the generated script will have a
DELETE command to remove the table data before the insertions.
@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
If these parameters are informed, the “path” to the database objects will be like this:
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.
- 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.
- 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.
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.
Questions, suggestions, and bug fixes are welcome!