I cook and take food photo. I build databases and love to talk about it.

Saturday, 21 February 2015

On 15:16 by Unknown in ,    7 comments
At my current workplace, I use SQL Server 2014 and SSIS (SQL Server Integration Services) to do all database and ETL (Extract Transform Load) development work. Recently, I have started a degree course at University of Hertfordshire, where we use Oracle.... #OutofmyComfortzone already.

One of the fellow student posted a question on the university class discussion forum, asking if someone knows how to import data from Microsoft Excel spreadsheet to Oracle. I usually read every single post on the university forum and try to help if I know the answer on top of my head, if not, then first I learn myself and then reply on the forum with the answer. I find this the best way to learn something new everyday.

For this demo, I have used Oracle Developer Version and Microsoft Excel 2007. To get started, lets create a simple Customer table first, and click here to download the Excel source file Customers.xls.

  CustomerID NUMBER(10) NOT NULL,
  FirstName VARCHAR2(35),
  LastName VARCHAR2(35),
  Town VARCHAR2(50),
  AccountCreationDate DATE,

After refreshing (press F5) the table list on the left pane under Connections list, right click on the table name Customers and select Import Data. 

Follow the step by step Oracle Developer import wizard instructions below to import data from Excel spreadsheet to a Oracle database table

Locate the Excel source file
After selecting the Excel file click Next and follow on screen wizard instructions.

To map Excel date to Oracle Date format amend the format field to dd/mm/yyyy

Click on Finish



Wednesday, 29 October 2014

On 22:29 by Unknown in    21 comments
I love finding tips and tricks online. Over the years, I have made a daily habit of reading database related articles, technical, non-technical or even food blogs online. Every week, I will be sharing my reading links here.

Wednesday, 22 October 2014

On 20:29 by Unknown in ,    4 comments
Exciting news first, I have started my new job as a SQL Developer (view my LinkedIn profile) this week. Today is my day 3 at work, and I am still as excited as I was on my Day 1. I am quite comfortable using Visual Studio 2008 to design and deploy SQL Server Integration Services (SSIS) packages. This morning, I have installed Visual Studio 2013 on my work machine to look at some of the SSIS 2014 packages designed by my colleagues. After opening one of the package, first thing I have noticed, that the usual Visual Studio Toolbox is empty, instead all Toolbox items required to design SSIS packages are in the SSIS Toolbox. While checking the all new Visual Studio environment, I have somehow managed to close the SSIS Toolbox. I know it is not a big deal, but if you are not familiar with the Visual Studio 2013, you may find it annoying to open SSIS Toolbox again. There are several ways to open the SSIS Toolbox, I am still looking for the keyboard shortcuts. 

Visual Studio 2013 - The SSIS Toolbox
Visual Studio 2013 - The SSIS Toolbox

Visual Studio 2013 - Empty Toolbox
Visual Studio 2013 - Empty Toolbox

Visual Studio 2013 The SSIS Toolbox

Tuesday, 21 October 2014

Wednesday, 8 October 2014

Monday, 6 October 2014

On 21:08 by Unknown in    4 comments
DB Designer Fork, an open source database Entity Relationship designer and database reverse engineering tool. Easy to use and capable of generating SQL DDL scripts (including indices and FKs) for SQL Server, Oracle, MySQL, FireBird and SQLite. The best feature I like is the power to connect to a database server and run query without opening SQL Server Management Studio or any other software. Currently, I am working on a SQL Server database project to build a complex finance database. I have designed entire database relationships using this tool. If your development team is on a tight budget then I would say DB Designer Fork is the must have tool.

DB Designer Fork Open Source SQL Server, Oracle, MySQL, SQLite database entity relationship designer

DB Designer Fork website: http://dbdesigner-fork.sourceforge.net/

Monday, 29 September 2014

On 17:12 by Unknown in    2 comments

SQL Server Random Records for Data Sampling

If you ever need to check your SQL Server database table for random records, here is the sample query below to return TOP 10 random rows from a database table.

 -- Replace   with your table name
FROM     <your TABLE NAME>
ORDER BY newid();

SQL Server - Random Records from a Table for Data Sampling

To read my more SQL Server database related blogs, click here