:::: MENU ::::

Posts Categorized / MS Excel

  • Oct 18 / 2012
  • 0
dbDigger, MS Excel, T-SQL Tips and Tricks

Build queries by concatenating cells in excel sheet

Often it is required to update the provided values based on specific data in excel sheet. For example we have two column data in excel sheet. Columns are ID and EmpName. EmpNames from excel sheet should be the updated values for ID  provided in excel sheet.
Required query for updating the name to ‘Shehzad’ would be as following for ID = 2

Update EMPS set EmpName = 'Shehzad' where ID = 2
GO

It would be time consuming to manually build the query or first import the data in a table for required update operation. The easy way would be to create the query by concatenating the cells and strings inside the excel sheet as shown below.

Build queries by concatenating cells in excel sheet

 

I have used following formula to build the required query for my scenario
= “Update emps set name = ‘” & B2 &”‘ where ID = “& A2
Simply enclose the string in quotation marks and join the cells and strings through & sign. After implementing the formula just drag the cell down to all rows down.

There is another function provided for same purpose. Using concatenate function would also complete the task as shown below.

Build queries by concatenating cells in excel sheet through concatenate function

In this case i have used the concatenate function in the following way
=CONCATENATE(“Update emps set EmpName = ‘”,B2,”‘ where ID = “,A2) 
It would produce the same results as by first method.
By using these simple techs a lot of time and effort may be saved.

Consult us to explore the Databases. Contact us