Lets See how you going to update or add thousands of records with in couple of minutes using Excel.
Requirements
1. Exact SQL Query to Update or Add the SQL table.
2. String Concatenation in Excel
1. Exact SQL Query to Update or Add the SQL table
If you are familiar with SQL queries writing an query for add or update for a given table is not that hard. But I am too lazy to do that as more than query skills it requires to get all the column names and give the values for each column accordingly. Most of the present database management tools (MSSQL management tools or MySQL managements tools) gives add, update and other SQL queries for a given table. Most of the cases you just have to right click on the table and select the query you want and BINGO it will generate the query for you. Copy the SQL Query that was generate or you have written by your self.
2. String Concatenation in Excel
This is the tricky part and I have couple of secrets to revealed here. So it is better to go through a example and you can apply it to any SQL Query.
1. Open up new Excel work sheet and paste your query there. It will be in single cell.
In this case I will take a simple MS SQL query that will add the item type for set given item in ItemType table.
INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('1000','S1');
2. Then Identify the areas that will change from record to record and the areas that will stay the same for all the records.
I will bold all the area that are dynamic.
INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('1000','S1');
3. Now start taking each section of the query and paste it in different cells.
Sections for our example will be like this
Section 1 - INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('
Section 2- 1000
Section 3 - ','
Section 4 - S1
Section 5 - );
So each section should go into a different cell in the same row.
Tip You will notice that when ever you try to copy Section 3 (',') the first single quote will get disappear in Excel. You have to enter another extra quote to fix this problem. So Section 3 will be rewritten as follows.
Section 3 - '','
4. Now you have to recreate the SQL Query that you copied using Excel String Concatenation.
In the same row leave one cell space after the query and select the next cell. Click on Insert Function button (fx) and it will POP up a Windows. Select Text and then Concatenate. Another window will be open with Text1 Text2 fields. Apply each section for each text field in the same order.The final result of String Concatenation should give you the exact same SQL Query you start with.
5. You are pretty close to finish updating couple thousands of records in your database. Now you have to get all the relevant record in your database and updated fields in Excel.
Tip: Always try to do or get the updated work in Excel. You can do that by providing the existing data in a Excel Sheet or any data entry or someone who is going to update your records.
Otherwise you have to copy and paste the data from another file type to excel and clean it up a little bit.
Anyway Excel is quite powerful in sorting, grouping filtering and changing thousands of fields as the same time. Speed and the options would be much better than your application (in most of the cases). Anyway it is a different set of skill which we will go through later.
6. Copy and paste fields in appropriate columns in your excel.
In our example I have only two fields and I will copy the set of Item numbers and Item Type Code is the two columns that we bold in Step 2.
7. Select each cell including the Concatenated one (but excluding the dynamically changing cells) and click on the small black box at the bottom right hand corner
ner and drag it down until the row that last dynamic field is populated. It is some thing like if you have 9000 records you need to fill other cells (SQL Query) in those 9000 cells.Warning : Always remember to Back up your database before applying this step.
8. Finally you all individual SQL query for all your record
s in the Concatenated field. Updating or Adding it really dose not matter. Select all the rows and copy and paste it to the SQL Query execution window. and Execute the Query. Either it will add all the records or updated.That's it!
Warning : Always remember to Back up your database before applying this step.
Update queries may update the whole table or wrong row base on the conditions you have provided.
You can do deleting certain rows from database tables using this method as well. Again it will have the same risk deleting the whole table or wrong rows base on the condition you specify. Always double check before you execute the query.
Use this hack make you life easy and share your experience with us.
