Multiple Insert

[Chance1234]

Hi

i have for arguments sake table B and table A
Table A contains a list of clients and table B contains a list of activitys for that client

now what i want to do in my data transformation is look up a default client in table B so i have a list of default activitys, then for each client in table A add in records into table b with these activitys

I cant see how i can do a multiple insert in DTS without creating a data driven query for each seperate activity

Filmmaker, gentleman and proffesional drinker

http://public.fotki.com/chance1234/

http://journals.fotki.com/chance1234/

 

[Chance1234]

Just reading through my post and not sure it makes sense so to try and explain it a bit better


TABLE A
**********
CLIENT
------
frank
sam
Dean



TABLE B
**********
CLIENT ACTIVTY
------ -------
Default 1
Default 2
Default 3
Bob 1
bob 2
bob 3
sid 1
sid 2
sid 3

what i want to do in my DTS package is for each client in Table A set them up in Table B with all the activitys listed for the default client, so the end result would be

TABLE B
**********
CLIENT ACTIVTY
------ -------
Default 1
Default 2
Default 3
Bob 1
bob 2
bob 3
sid 1
sid 2
sid 3
frank 1
frank 2
frank 3
sam 1
sam 2
sam 3
dean 1
dean 2
dean 3

hope that makes it a bit clearer

Filmmaker, gentleman and proffesional drinker

http://public.fotki.com/chance1234/

http://journals.fotki.com/chance1234/

 

[Chance1234]

oh and just to add on the pier de resistance table A and B are on different servers

Filmmaker, gentleman and proffesional drinker

http://public.fotki.com/chance1234/

http://journals.fotki.com/chance1234/

 

[nigelrivett]

insert tableB
(CLIENT, ACTIVITY)
select a.Client, b.ACTIVITY
from tableA a
cross join tableb.B b
left outer join tableb b2
on a.CLIENT = b2.CLIENT
and b.ACTIVITY = b2.ACTIVITY
where b.CLIENT = 'default'
and b2.CLIENT is null

Put that in an sql task - or better in an sp and call it.
You might now consider whether you need dts.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

>> oh and just to add on the pier de resistance table A and B are on different servers

You can define a linked server to perform the query but probably better is to transfer TableA to the the other server - either into a temp table or permanent one.
If you have an identity or lastupdated column on the source then you can do incremental updates to the remote table.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Chance1234]

Hi Nigel,

definetly a DTS as this is one part of a massive chain of Data driven querys

Filmmaker, gentleman and proffesional drinker

http://public.fotki.com/chance1234/

http://journals.fotki.com/chance1234/

 

[nigelrivett]

>> definetly a DTS as this is one part of a massive chain of Data driven querys

Hmm using dts as a control mechanism.
Oh well - good luck.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Chance1234]

????????????????????????????????????

Filmmaker, gentleman and proffesional drinker

http://public.fotki.com/chance1234/

http://journals.fotki.com/chance1234/