How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to insert multiple values to a column at a time?

Hi all,

I was looking to insert multiple values to a single column at a time in SQl.

Any help pleas!

Thank you

Ephi:

What exactly do you mean by "multiple values"? Different data types? A vector? An array? What exactly.

Dave

|||

Lets say I have a single Column called X and I want to insert multiple values into X like (1,2,3) at a time through using the insert statement.

Thank you in advance.

|||Hi,

In reality it is not acceptable according to data normalization rules.
I.e. if you want to have such behavior you should better create an additional table and have foreign constraints mapping to it.
Some example:

table_x(
field_1 .....,
field_2 .....,
field_in_which_you_want_to_have_multiple_values.....
);

table_y(
value_identifier .....,
value nvarchar(1024) ....
);

So you firstly insert several values to table 'table_y' and then just add
value_identifier to a 'table_x.field_in_which_you_want_to_have_multiple_values'|||

do you mean you want to create multiple rows, with one row for each value, using a single Insert statement?

This is not possible. Insert only creates one row in the table.

Unless, of course, you are inserting into one table using the values from another table, in which case you can use the insert...select syntax.

Normal insert syntax:

insert table_name
(col1, col2, col3)
values
(val1, val2, val3)

only inserts one row.

|||I would recommend you use Itzik Ben-Gan's Split function:

CREATE FUNCTION dbo.fn_SplitTSQL
(@.arr NVARCHAR(MAX), @.separator NVARCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@.arr, n), @.separator, '')) + 1 AS pos,
SUBSTRING(@.arr, n,
CHARINDEX(@.separator, @.arr + @.separator, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@.arr)
AND SUBSTRING(@.separator + @.arr, n, 1) = @.separator;Once you've got this, you could do something like:

insert into mytable (col1, col2, col3)
select 'Val1', 'Val2', element
from dbo.fn_SplitTSQL(N'1,2,3',N',')

This should handle it nicely for you. Oh yes, and you'll need a table called Nums with a field called 'n', which you have populated from 1 to some arbitrarily large number. 1000 might be big enough for most of your uses...

There's more on this at:
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

Robsql

How to insert multiple values in a single column

I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries.

In Col1, Col2 and Col3 there can be any number of values.

Company

BankCol1Col2Col3AstroTechICICI

123

5

34

MindTreeHDFC

54

8

why don't you want to use a sub-report?

|||

It takes very long time, because i have 17 columns in the report.. its not fisible to insert 15 sub-reports

|||

Ah I understand, and agree. Not sure how else to implement this though...could you insert another table into your main table's cells?

|||

Not resolved yet... any ideas...

|||

One idea would be to build your data using a view. Capture the main data (Company, Bank, ID) and then capture each columns' data (based on the ID in first query). Select from the view.

I think your data would be more like:

ID Company Bank ManagerNames BranchOffices TopSalesNames

1 CompanyA BankOne Jerry, Ted, Lisa Omaha, Chicago Fred, Mary

2 CompanyB BankTwo Paul Lincoln, Springfield, Florence William, John

Not sure if this meets your needs. You may be able to format the multi-value rows once in the report. Hope this helps.

|||

thanks... I will try this

How to insert multiple rows using stored procedure

How to insert multiple rows with using a single stored procedure and favourably as an atomic process?You need to inlude SAVE POINT in your T-SQL code so a rollback or interruption will not take the Transaction back to the beginnning. Like the sample below. Hope this helps.

SAVE TRANSACTION SavepointName
IF @.@.error= some Error
BEGIN
ROLLBACK TRANSACTION SavepointName
COMMIT TRANSACTION
END

Kind regards,
Gift Peddie|||You have a couple of options here:

1) created a delimited key,value pair and parse it in the proc
2) package the values as an xml chunk and use OPENXML to shred the doc and perform the insert

I prefer option 2.

And with regards to atomicity, you wrap 1 or 2 in a BEGIN TRAN, COMMIT or ABORT in the proc.|||Since I never used OPEN XML can you give me a link to a good tutorial how to pass xml from .net code to sql sp..

Thanks|||Have a look at the following article:

Decomposing with OpenXML

how to insert multiple records into table

insert into table1 (colname) values (value1)

can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?

We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take

value1, value2, value3 ...

from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

What is the source of the data? from another table?

|||

If you are taking values from another table then you can insert multiple records.

Any ways i think it internally dosen't make much difference if you use multiple records or single record multiply internall it will fire that many insert statements only.

I find this intresting article guess it will help

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

|||

U want place into the same table r other clarity is required

Insert into x select * from x

insert into x select * from y

If u want to copy the data of entire table use * other wise U have to specify column clause

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I hope I'm getting your question right.

Are you looking for something like

insert into table1 ( colname )
select value1 union all
select value2 union all
.
.
.
select valueN

If you can specify your problem in details then we can help you better.

|||

Thank everyone for your input. What I want was taking the contents of multiple records delimited by delimiter from clipboard as a string, then insert into a table. I complished by writing a user function which takes a string, then output a table with multiple records, it works as

insert into table1 (ID)

select values from fn_StringToTable(@.myString, @.delimiter) -- here @.myString='00a1,00a2,00a3,...'; @.delimiter=','

output table1 got the IDs from the clipboard which has'00a1,00a2,00a3,...

Thanks

How to Insert Multiple Records into sql 2000 table at once ?

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
--order no --orderno
--issuedate --itemname
--supplier --desccription
--amount --price
--Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!See links below:

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.eggheadcafe.com/articles/20030627c.asp|||hi, thanks a lot,I will browse the web page you give me .

How to include multiple membes in an expression?

Hello, I'm wondering how can I include multiple members from a dimension in a Calculated Measure Expression?

Good (single member) ([ActionGroup].[ActionTag].&[147300], [Measures].[Unique Visitors])

#Value Error (multiple members)

([ActionGroup].[ActionTag].&[147300].&[107139], [Measures].[Unique Visitors])

({[ActionGroup].[ActionTag].&[147300], [ActionGroup].[ActionTag].&[107139]}, [Measures].[Unique Visitors])

Any pointer is appreciated

-Lawrence

Is this what you are looking for ?

Aggregate({[ActionGroup].[ActionTag].&[147300], [ActionGroup].[ActionTag].&[107139]} , [Measures].[Unique Visitors])

How to import word documents into SQL Server

Hi All,

I need to import multiple word documents of same structure into SQL Server table. Could anyone suggest the way of doing this?

Thanks,

What do you mean, "the same structure"? Do you want to extract informaiton from the documents or just store each one as a BLOB? If the latter, the 'Import Column' component should work for you.

-Jamie

|||I mean i want to extract information from word documents into sql server tables.|||

Hemal Shah wrote:

I mean i want to extract information from word documents into sql server tables.

I'm not sure really. This isn't a common request seeing as Word documents are not structured files. The normal answer to extracting unstructured data using SSIS is that you're going to have to use code. You can download Primary Interop Assemblies for Office that enable you to interact with Word documents using dotnet code - hence you can use them in a script task/component.

-Jamie

HOW TO IMPLEMENT UPDATE INMEDIATE WITH ONE SUBSCRIBER AND MULTIPLE PUBLISHER

Hi, i have SQL2k SP4.
My replication topology is:
1 Subscriber central and 4 publisher (no conflic)
ok, the subscriber is necesary update inmediate to publicher data, for
example:
Pub A containt
Empresa_id # Sucursal_id # monto
-- -- --
1 1 100
2 1 200
Pub B containt
Empresa_id # Sucursal_id # monto
-- -- --
1 2 100
2 2 200
Subscriber containt
Empresa_id # Sucursal_id # monto
-- -- --
1 1 100
2 1 200
1 2 100
2 2 200
The PK is Empresa_id + Sucursal_id
ok.
I need the suscriber update inmediate to publicher, for example:
UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=2
The update is necesary distributed transaction to PUB B only.
UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=1
The update is necesary distributed transaction to PUB A only.
------
ok, whats is major method to implement the scenario?
Tks

Salu2
Microsoft MVP SQL Server
Culminis Speaker
make your central subscriber the publisher and use immediate updating on the
subscribers. Then make sure your polling interval is low. You should get the
majority of your inserts happening in seconds.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maxi" <maxi_da@.infovia.com.ar.sacame> wrote in message
news:u8qGG1BMHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hi, i have SQL2k SP4.
> My replication topology is:
> 1 Subscriber central and 4 publisher (no conflic)
> ok, the subscriber is necesary update inmediate to publicher data, for
> example:
> Pub A containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 1 100
> 2 1 200
> Pub B containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 2 100
> 2 2 200
>
> Subscriber containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 1 100
> 2 1 200
> 1 2 100
> 2 2 200
>
> The PK is Empresa_id + Sucursal_id
> ok.
> I need the suscriber update inmediate to publicher, for example:
> UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=2
> The update is necesary distributed transaction to PUB B only.
> UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=1
> The update is necesary distributed transaction to PUB A only.
> ------
> ok, whats is major method to implement the scenario?
> Tks
>
> --
> Salu2
> Microsoft MVP SQL Server
> Culminis Speaker
>
|||Hi, my central subscriber is make inmmediate update!
But it has but publisher (4) and it is needed that subscriber it updates
when suitable publisher with MSDTC. Single trigger updates one single one
and I need that depending on the filter she updates to publicher X
Salu2
Microsoft MVP SQL Server
Culminis Speaker
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:uMtXDBNMHHA.448@.TK2MSFTNGP04.phx.gbl...
> make your central subscriber the publisher and use immediate updating on
> the subscribers. Then make sure your polling interval is low. You should
> get the majority of your inserts happening in seconds.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Maxi" <maxi_da@.infovia.com.ar.sacame> wrote in message
> news:u8qGG1BMHHA.1240@.TK2MSFTNGP03.phx.gbl...
>

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

-

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

--

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

--

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~

How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

-

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

--

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

--

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~