A Smart way for creating a table (SQL) for Informatica guys!!

November 17, 2012

This was something i got to learn from my team mate. A very good and a simple way of making your work much easier.

So as the requirement said, create a SQL table and load the data from a MS Excel file. Say there were almost 50 columns and so i had a tiring and huge manual work of listing down the column names and then write a SQL script to create the required SQL table. Though i was sure, that once the table is ready i can very well make the Excel file to a tab delimited text file and later load the whole data through a mapping with source as the flat file(text file) and the target as my SQL table that i will be creating in SQL.

Not a big challenge, but needs a smart solution to handle the table creation having plenty of columns to be created. Here we have a better way to do this for the Informtica developers.

All we have to do is, just import the Flat file source into the informatica source analyzer and just drag drop the same Source object into our target designer,i mean copy the source as a target. Now we have a target created same as the source. And here is all we need to act smart, change the target Database Type to ‘Microsoft SQL Server’ and so we have made our Flat file target into a SQL server target table.

Now comes the trick of making things easier, just get on to Target menu -> Generate/Execute SQL.. and we have the wizard window to generate the SQL script for us. Since we need a Create table script, just have your options selected as is shown below.

And just a click on the Generate SQL File shall get the required script of creating a table having any number of columns. So here we are ready with a create table script to make our job easier. All we have to do here on is to get this running on the SQL Server Management studio to get the table created. And as all we know to populate this is pretty simple. We have our flat file source available and also the target. A mapping to load from source to target, execute it and we have the table created with the data populated.

Isn’t that simple!!?? Share and comment 🙂


2 Responses to “A Smart way for creating a table (SQL) for Informatica guys!!”

  1. Thanks a lot .. Nice Trick … I will be using it a lot now. Really useful. Just tried this on my laptop Infa 9.0.1 installation. 🙂

  2. nagesh kumar Says:

    well explained on dynamic way of creating tables when the source is flat file

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: