Create SQL Script by Excel

As a database developer, you always need to create sql script to change data for tables during a common database maintenance process. Writing such script could be very tedious as many columns contains fixed or changed values which have special rules. Here applying Excel to create those script would be very helpful. Let's firstly investigate following sql script code created by Excel.

INSERT INTO #DayNameTable([DayNumberOFWeek],[EnglishDayNameOfWeek],[SpanishDayNameOfWeek],[FrenchDayNameOfWeek]) values
    (1,'Sunday','Domingo','Dimanche'),
    (2,'Monday','Lunes','Lundi'),
    (3,'Tuesday','Martes','Mardi'),
    (4,'Wednesday','Miércoles','Mercredi'),
    (5,'Thursday','Jueves','Jeudi'),
    (6,'Friday','Viernes','Vendredi'),
    (7,'Saturday','Sábado','Samedi');

You can use Excel string function to create above script very easily based on table data. Following is the data list abstracted from original table, you can copy/paste from your query result to Excel directly.

DayNumberOFWeek	EnglishDayNameOfWeek	SpanishDayNameOfWeek	FrenchDayNameOfWeek
1	Sunday	Domingo	Dimanche
2	Monday	Lunes	Lundi
3	Tuesday	Martes	Mardi
4	Wednesday	Miércoles	Mercredi
5	Thursday	Jueves	Jeudi
6	Friday	Viernes	Vendredi
7	Saturday	Sábado	Samedi

The Excel expression to create above sql script would be :

=IF(NOT(ISBLANK(A2)),IF(A2=1, CONCATENATE("Insert into dbo.DImDate('DayNumberOFWeek','EnglishDayNameOfWeek','SpanishDayNameOfWeek','FrenchDayNameOfWeek') values(",A2,",'",B2,"','",C2,"','",D2,"'),"), CONCATENATE("(",A2,",'",B2,"','",C2,"','",D2,"');")))

It would check if the first row of column DayNumberOFWeek is empty, if not, it creates insert script based on the row data. Note when creating the insert script, for the 1st row, it will create full insert script with all available column name list and all column values; for the following rows, it just creates all column values.

After it's done, you just change the last comma to semi colon, and then you can applying the final sql script to your database directly.