Solved

Selective Insert into a new table from multiple tables SSIS

Posted on 2010-09-24
6
750 Views
Last Modified: 2013-11-10
I established a connection from oracle to SQL Server via SSIs package. There is  a new table sitting in SQL Server with no data. Data is to be transfered from SEVERAL DIFFERENT TABLES FROM ORACLE.
HOW DO I DO THAT?
I know how to do it from single table.
NOw i have speacial requirements for each column

1st column-- >  append of two columns from table 1
2nd column-->  change date type of a column from table 1
3rd columns ->  look for two columns in table 1 and using a switch case for every difrnt set of 2 values there exists a unique value in column 3rd column of table 2(look up table). so that unique value from table 4 is to be inserted.

more on 3rd column

table 1
C7 C8
a     2
a     5
c      6
g     8
a     5

Look up table

a 2 Good
a 5 Good
c 6  BAD
g 8 UGLY
a 5 GOOD

So for every record in  i would have a CASE saying

IF C7 = a AND C8 = 2 ....INSERT ..GOOD

I have just four cases though.

so  all those 1000s of records boil down to one of the four values in the corresponding column of target table in SQL Server.

When i did a direct transfer i have a query like

SELECT * FROM my_table_in_Oracle;

What do i have there now ??

and FYI the table is already created in SQL Server.

Do i do like

INSERT INTO TABLE VALUES
(
'      SELECT CONCAT(COL1,COL2) FROM Oracle.Table1       ',
'      SQL Code for second column depending on what needs to be inserted',
'      SQL Code for second third depending on what needs to be inserted '
)

Im sorry for making it too lengthy.
Is this the way to do it ?

Or is there a pre defined package in SSIS for this kind of slective inserts.

Thanks.

0
Comment
Question by:misermice
6 Comments
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33754715
maybe the following code will help you:
INSERT INTO my_table_in_SQL
(
	column_Name1
	, column_Name2
	, column_Name3
)
SELECT 
	COLUMN1 + ' ' +COLUMN2 -- WILL APPEND COLUMN 1 AND 2 WHIT A SPACE BETWEEN 
	, CONVERT(VARCHAR(100), COLUMN_TO_CHANGE_DATATYPE) -- WILL CONVERT DE DATA TYPE TO VARCHAR(100)
	, CASE  -- EXEMPLE OF A CASE IN T-SQL
		WHEN C7 = a AND C8 = 2 THEN 'GOOD'
		WHEN C7 = B AND C8 = 1 THEN 'BAD'
		WHEN C7 = C AND C8 = 22 THEN 'UGGLY'
		WHEN C7 = D AND C8 = 4 THEN 'NASTY'
		ELSE 'ERROR'
	  END
FROM 
	TABLE1 T1

Open in new window

0
 

Accepted Solution

by:
ysovol earned 250 total points
ID: 33754841
Hi,

Firstly you are not clear about Column 2.
Secondly, the way you should go if you want to populate a table from information based on different source table is:
INSERT INTO DESTTABNAME (Col1, Col2...)
SELECT FROM SOURCEVIEWNAME (Col1, Col2...)

where DESTTABNAME is the table you want to be filled with data and SOURCEVIEWNAME is a view you have to create that combines & transforms the data (from the various SOURCE tables) in such a form that match the columns of DESTTABNAME.

In this case you would be writing something like:

insert into DestTableName (col1, col2, col3...)
select col1 || col2, some_column_from_table_1, t2.col3
from table1 t1, table2 t2
where t1.c7 = t2.col1(+)
and t1.c8 = t2.col2(+)

joining table1 with table2 in order to get your lookup value (the t2.col3 - Good, Bad)
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 250 total points
ID: 33755854
As you are using SSIS..you wouldn't need an insert statement... you can follow the below steps

1. Source - Oracle Database
2. Use Derived Column transformation Task (you can use below expressions)
    ---- ColumnName1 + ColumnName 2  (to concatenate your columns ,make sure that data types are same)
    ---- (Dt_STR,255,1252) ColumnName3  (change the data type conversion from DT_STR to one you need accordingly)
3. Use a lookup (use a sql statement and get the columns you need.. )
 ---- create a connection to your Lookup table, select SQl statement from dropdown and use SQl Like below
            SELECT C7,C*,C9 FROM LookUpTableName
 ---- in the Column Mappings map the Columns that you want to lookup on and then select the output column

4. Use a OLE DB destination --Map it to your destination Table and map the columns accordingly... The Derived Column  (ColumnName1 + ColumnName 2) should be mapped to Column1 of your destination and 2nd derived column should be mapped to second columns and c9 of lookup should be mapped to column3 of your destination table

here are some links that will help you

Lookup -- http://sqlblog.com/blogs/andy_leonard/archive/2010/02/16/ssis-snack-lookup-transformations-101.aspx
http://www.sqlshare.com/ssis-lookup-transform-basics_632.aspx

Derived Column - http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx
 http://www.ssistalk.com/2007/01/23/derived-column/

 
0
 
LVL 1

Author Comment

by:misermice
ID: 33980593
hhh
0
 
LVL 1

Author Closing Comment

by:misermice
ID: 34504739
Thanks for the detailed explanation.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now