Transferring rows from one table to 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Transferring rows from one table to 2 tables

Author

Topic

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 03:20:12

Hi,

I have a table in Sql 2005 called
Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode

I have to transfer the records into 2 tables

CustomerMaster
CustomerId
CustomerStCode

CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank

I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer.

How to do this?

thanks

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 03:22:02

What have you tried so far?

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 04:23:14

Hi kristen,

This is what i did. I took a Oledb Source and a Oledb destination. Then flushed the rows from Customers to CustomerMaster. But how can i populate CustomerDetails also at the same time :(

Customers and CustomerMaster/CustomerDetails are in two different databases.

I also want to mention that the CustomerId in Customers will not be used in CustomerMaster and CustomerDetails. When a row is inserted into CustomerMaster, the id is autogenerated.

thanks.

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 04:35:37

"But how can i populate CustomerDetails also at the same time "

Ah, OK, I get you.

You can't, you have to use two separate INSERT statements.

But what you can do is to use

BEGIN TRANSACTION

First insert

If no error Second insert

If error ROLLBACK else COMMIT

so that the two inserts will happen as a single "atomic" operation.

Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 04:39:09

Ok..I have added a data flow task.. inside a DFT I took a Oledb Source and a Oledb destination..so do i insert a component(which is ?) in between them? Moreover how will i make sure that the correct row got inserted into 2 different tables..I know its a basic question but i am not clear.

Kristen do u want me to give you some sample data so that u can help me better

thanks

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 05:39:16

"how will i make sure that the correct row got inserted into 2 different tables"

I would put the data (or perhaps just the IDs [primary key fields]) into a temporary table first, and then use that to control the two separate inserts. That way something else that got inserted "during" this operation won't be a candidate for the second insert.

"do u want me to give you some sample data so that u can help me better"

I'm not in a position to do this for you. I can help if you get stuck, but it does seem to me that you don't yet have the skills you need, and I can't "train" you via SQL Team, only provide advice.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 05:50:28

With the new OUTPUT in SQL Server 2005, you can now insert into two tables simultaneously!
See http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

E 12°55'05.25"
N 56°04'39.16"

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 06:00:39

thanks peso..i will check this out..Can i try this and post the query i tried in T-Sql forums? thanks.

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 06:13:04

You can post any T-SQL stuff you wish here. My earlier point is that we can't train you, only advise you, and if we wind up writing your application for you folk here are likely to get fed up and stop helping you.

IMHO you need to understand more than you do in order to write the application you are building.

Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 06:26:23

Yes kristen that's very correct..i have joined this field just 5 minths ago and am new to sql..but just can't help. If i am given a task, i have to deliver by any means..:)But you guys have been a great help last time and i totally agree to what you say..Just wanted to know if can u help me out one last time..next time this kind of work comes..i will reject it no matter what..I need to get my basics correct before leaping into something that's not in my limits.

can i post the entire problem with the scripts. I will do that only if you say yes?

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 06:37:44

You can post the entire script, I can't see that will offend anyone here, but I doubt I (personally) will have time to help. IMO it looks to be mostly application stuff, and IMO this should be done in SQL, so you are coming at it from an angle that I'm not comfortable, nor familiar, with.

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 06:54:25

thanks..i posted it over here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91268

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources