Using the CONCATENATE function in Microsoft Excel
Today, I had a task of building SQL insert queries from an Excel sheet. The Excel sheet contained two columns 'Database_name' and 'Database_admin'. My purpose was to insert every pair of such values into a table which had two columns of varchar type.
So, I used the concatenate function in Excel as follows. Say, the 'Database_name' list is in column A of the Excel, and the 'Database_admin' list is in column B. I used the an empty column, say D, and clicked on the first cell, say D1 in the row containing the first pair, and I entered:
=CONCATENATE("INSERT INTO TABLE_NAME_HERE VALUES ('",A1,"','",B1,"');")
Press enter. So, if A1 contained 'db1' and B1 contained 'admin1', then D1 would be
INSERT INTO TABLE_NAME_HERE VALUES('db1','admin1');
Then I dragged the plus icon at the lower left of D1 till where the records ended. This did it for me.
A WORD OF CAUTION: This works on Microsoft Excel 2013. If you are on Microsoft Excel 2007, then you will have to substitute the commas(,) with semi-colons(;) i.e.
=CONCATENATE("INSERT INTO TABLE_NAME_HERE VALUES ('";A1;"','";B1;"');")
I hope this helps you. Cheers!