Monday, June 12, 2017

How to Generate SQL Query in excel sheet two column



Today I have faced one problem in excel sheet. I have a situation need to update user email id details from excel to my client database. The users email list is more than 1000 users.

For this update to excel we can do line by line update to the database or import into database and write the join query for update. But the simple way is we can generate INSERT/UPDATE SQL query and execute in to the database. For my situation I have already user data in the database. So I want to update the emailID based on the employeeID contition.

For that the below Excel comment will help to generate the SQL UPDATE query with simple steps and I can update with in 5mintus.

=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")


Excel Sheet:

Emp ID Name EMail id Project
1110 Karthik Karthik@gmail.com Sprots Domain
1111 Kumar Kumar@gmail.com Sprots Domain
1112 Vijay Vijay@gmail.com Sprots Domain
1113 Yuvan Yuvan@gmail.com Education Domain


Excel Sheet Query Generator:
=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Output SQL Query :
SQL Query
UPDATE tb_employee set EMPL_EMAILID= ('Karthik@gmail.com') WHERE EMPL_ID= (1110);
UPDATE tb_employee set EMPL_EMAILID= ('Kumar@gmail.com') WHERE EMPL_ID= (1111);
UPDATE tb_employee set EMPL_EMAILID= ('Vijay@gmail.com') WHERE EMPL_ID= (1112);
UPDATE tb_employee set EMPL_EMAILID= ('Yuvan@gmail.com') WHERE EMPL_ID= (1113);

Finlay copy all the sql query from excel sheet and execute into the database. Its take 5 mints to complete your task.

Try this and let me know your feedback.

No comments: