When Python Meets SQL–part 5: read/write multiple sheets from one excel file

After I finished re-testing this code, I suddenly realized that SQL connection and SQL commands did not appear. I am happy that this post in only name as part 5. The previous four posts all have SQL inside. This one is just to show how flexible pandas could be. When I had an excel file with several tabs and the data structure on every tab are same, this code was used to save time and to avoid mistakes:

Continue reading “When Python Meets SQL–part 5: read/write multiple sheets from one excel file”

When Python Meets SQL-part 3: load excel file data into SQL Server without creating table

At the beginning of my Python ETL journey, I created tables in SQL server and insert to those tables. Back then, I thought this is the only way. During my work using pySpark, I used pySpark to write SQL tables from pySpark dataframe. The best thing is I don’t need to create tables pySpark does all for me. (I will wrote pySpark codes later). That good experience makes me think: is there a better way in python? Is there a way to write data into SQL server without creating table?

My best friend google told me: yes. And here is how:

Continue reading “When Python Meets SQL-part 3: load excel file data into SQL Server without creating table”

When Python Meets SQL –part 2: loop to ETL multiple tables

In part 1 of this series, I mentioned loop after I failed to fast load data to sql server in my test code with my personal computer. In this post, I will show the advantage of using loop in python to move multiple tables/queries. The beauty of learning and using python is we can skip many mouse clicks in SQL server Import and Export Wizard. Let’s begin.

Continue reading “When Python Meets SQL –part 2: loop to ETL multiple tables”

When Python Meets SQL–part 1: move data between two SQL servers with fast load

I will organize several code that I have used in my daily work to do simple ETLs. This post will be first one: how to move table (or query result from one SQL server) to another. Even though the task could be handled pretty easily in SSIS and SS Import and Export Wizard, the python code provides the perfect starting point for SQL developers to use python.

Continue reading “When Python Meets SQL–part 1: move data between two SQL servers with fast load”

Pick up blogging again

Time really flies when I am having fun. I can’t believe that it has been more than one years since my last post. I had a lot of fun during this period of time: implementing python and PySpark in my daily work, picking up data science codes, learning graph, giving presentation at local SQL server user group meeting… … As a result, I haven’t get anything done with this blog.

Continue reading “Pick up blogging again”

Trick SQL Server to use the best Execution Plan

What is the two most important words that one should look for in SQL Server Execution plan?  Scan ! Seek!

SCAN a big table is a disaster. The rescue is: try your best to let SQL  Server SEEK a big table.

(William Wolf taught me this many many years ago at a SQL Saturday meeting. This knowledge is so powerful and it makes one of my query work so good that I want to share with you.)

Continue reading “Trick SQL Server to use the best Execution Plan”

Look at me! I made the fire!

In the movie Cast Away, the character (by Tom Hanks) danced and jumped around a bonfire on beach and shouted many times: “look at me! I made the fire!”, even though he knew that nobody could look at him at that isolated, far away island. He simply could not stop celebrating his exciting achievement. That is exactly same feeling on a late Friday afternoon, after I figure out how to solve two of my problems at same time. Those two problems are: first. let SSRS pass a parameter to a query in DB2, and second, that parameter could be one or several account_ID.

Continue reading “Look at me! I made the fire!”

Scheduling SQL Job with SSRS

Imagine this: you are  at an technical interview of a SQL developer position. The interviewer asks: tell me someway to schedule an SQL server job without using SQL Server agent. If I am at this kind of situation, I will be surprised by the question.  I probably will miss the question and only focus on find out the reason not to use SQL Server Agent.

The fun thing is: I am going to share our unconventional ways to schedule SQL jobs (so that you won’t be surprised when you are asked about it)

Continue reading “Scheduling SQL Job with SSRS”

Dynamic! Dynamic!

Dynamic is one of my favorite search words. It is the opposite of “static”. A lot of time, SQL statements requires static input, such as column name and table name in SELECT.  However, Dynamic SQL gives a way to overcome this requirement.  This post I will share with you other two usages of ‘dynamic’.

Continue reading “Dynamic! Dynamic!”