Solved

Selective Insert into a new table from multiple tables SSIS

Posted on 2010-09-24
6
752 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 36
SQL Server - Set Field Values ito Zero Based on Related Table 4 27
SQL2016 to ORACLE11G linked-server 6 15
syntax sql error 2 13
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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