One source two destinations

Question

Post reply

One source two destinations

lalit-450914

SSC-Addicted

Points: 462
More actions
October 19, 2007 at 2:35 am

#122044

Hi,
I have a table in Sql 2005 called
Customers
CustomerName
CustomerAge
CustomerRank
CustomerStCode
I have to transfer the records into 2 tables
CustomerMaster
CustomerId (identity)
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. Similarly for all other rows in Customers.
Customers and CustomerMaster/CustomerDetails are in two different databases.
How to do this?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Something along the lines of:

DROP TABLE dbo.CustomerDetails ;

GO

SELECT IDENTITY ( INT, 1,1 ) AS CustomerId

, Customers.CustomerName

, Customers.CustomerAge

, Customers.CustomerRank

INTO dbo.CustomerDetails

FROM dbo.Customers

GO

SET IDENTITY_INSERT dbo.CustomerMaster ON

GO

INSERT INTO dbo.CustomerMaster

( CustomerId

, CustomerStCode

)

SELECT cd.CustomerId

, c.CustomerStCode

FROM dbo.Customers AS c

JOIN dbo.CustomerDetails AS cd ON c.CustomerName = cd.CustomerName

GO

SET IDENTITY_INSERT dbo.CustomerMaster OFF

Depending on the size of the tables you may as well add some indexes before populating the dbo.CustomerMaster table.

This assumes that the customer name identifies the customer, which, considering your original table, should be the case.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

lalit-450914 SSC-Addicted Points: 462 More actions · Answer 2

Hi Andras,

thanks for your time. Are you suggesting inserting into CustomerDetails first and then into CustomerMaster. That may give a constraint error as the CustomerDetails holds customerid as the foreign key (primary key being in the CustomerMaster table).

Sorry if i have not followed your suggestion correctly but could you explain this a bit with just a few words for me to follow.

Secondly i cannot drop any table as the table already contains previous data. The flow is ..i populate a set of files from access db to staging table Customers(Sql server)..From Customers the data goes to CustomerMaster/Details.

thanks a ton

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Hi Lalit,

the reason fro populating CustomerDetails first, is that your CustomerMaster table does not have anything to identify the original customer. It only contains the identifier of this customer, but this identifier would have been generated anyway, since it does not yet exist. So the approach I've shown is to create the identifier in the CustomerDetails table. Since this table has all the details to identify your customer in the original Customers table. Once you have this, you can look up the remaining items (CustomerStCode) from the original Customers table. You would need to use the identity insert on, because the identifier for the customer is generated in the details table, and according to your schema you have set the identity property on the CustomerMaster table.

Concerning dropping tables, you can always use temporary tables and then just copy the data. I used select into because it can use Identity in it :). You could just select into a new table temporary table, and then just select from there into the original table.

Concerning the foreign key, you could either disable it (NOCHECK, then enable with with WITH CHECK CHECK (the two CHECKs is not a mistake you need both (http://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/))

or, if you go for the temporary table approach, then you create the CustomerDetails contents in a temporary table, based on that populate the CustomerMaster table, and then copy to the CustomerDetails from the temporary table.

Also, since you already have data in some of these tables you can set the identity seed in the SELECT IDENTITY ( INT, 1,1 ) statement so that the generated identifier will not conflict. This you need to set every time you import the data.

ps: (although I'm sure you are doing this, but it is always worth to remind people: do make a backup :))

Another thing, have you considered using SSIS?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

lalit-450914 SSC-Addicted Points: 462 More actions · Answer 4

hi,

thanks again for the help.

I am using SSIS 🙂 to do all of this and that is why need a SSIS specific solution to this..

I have added a data flow task.. inside a DFT I took a Oledb Source and a Oledb destination. Then flushed the rows from Customers to CustomerMaster. My doubt was how can i populate CustomerDetails also at the same time

So i get your solution now..Which component in SSIS do you recommend to use in DFT to do this specific thing ?

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

lalit (10/19/2007)
hi,
thanks again for the help.
I am using SSIS 🙂 to do all of this and that is why need a SSIS specific solution to this..
I have added a data flow task.. inside a DFT I took a Oledb Source and a Oledb destination. Then flushed the rows from Customers to CustomerMaster. My doubt was how can i populate CustomerDetails also at the same time
So i get your solution now..Which component in SSIS do you recommend to use in DFT to do this specific thing ?

Hi Lalit,

Glad I could help. Concerning SSIS components for this task I let others suggest one (I do not use SSIS very frequently).

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software