asked on

Selective Insert into a new table from multiple tables SSIS

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.

Cboudroz

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

ASKER CERTIFIED SOLUTION

ysovol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION