SQL Tips/Tricks 2

Excel is an excellent tool for generating sql scripts of reference tables. Besides being easy to generate the scripts, it’s also easy for the client to manipulate and update the reference information (almost always, it’s the format used for database like information for clients).

So for an excel with the following structure

TableX
ID	Name	Date1	UserId
1	Abc	10-01-2009	Alc
2	Def	10-01-2009	Xyz

where the $A$1 is the table name and the columns name are on row 2 (ID, Name, Date1, UserId), inserting the formula on column 6 (or bigger) without newlines:

=CONCATENATE("INSERT INTO ";$A$1;"(";A$2;",";B$2;",";C$2;",";D$2;") VALUES(";A3;", '";B3;"', '";TEXT(C3;"aaaa-mm-dd");"', '";D3;"')")

yields the following result:

INSERT INTO TableX(ID,Name,Date1,UserId) VALUES(1, 'Abc', '2009-01-10', 'Alc')

 

What the formula  is doing is concatenating “INSERT INTO”, the table name ($A$1), the parentesis, column names (A$2, B$2, …), the string VALUES, and the actual values separated by commas and delimited with ‘ when appropriate (string or date values).

So to generate the script is just a matter of copying the formula for all the rows with values (double clicking the cross symbol on the right lower corner of the cell, and it repeats itself according to the adjacent cell, or pulling down the intended number of rows):

And the end result is this:

And that’s it. A script ready for pasting in a SQL file and applying to a DB.

Marcas Technorati: SQL,Programming