insert/select

I am trying to find an easier way to handle my insert/select statement. If
I have the following tables and query - Is there a way to do this in one
statement?
create table Master(
MasterKey int,
)
Create table SubTable1(
MasterKey int,
PK int,
Data varChar(1000)
)
Create table SubTable2(
MasterKey int,
PK int,
Data varChar(1000
)
Master Table
MasterKey Priority
1 0
2 0
3 1
4 0
5 0
6 0
SubTable1
Empty
SubTable2
MasterKey PK
1 1
1 1
1 2
2 3
2 3
3 4
3 4
3 4
4 4
4 4
4 5
4 5
What I want to be able to do is move data from SubTable2 to SubTable1
I tried to do something like:
Select @.MasterKey from Master where Priority = 1 (this would give me
a MasterKey of 3)
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK = 4
This would move/create 5 records with a @.MasterKey of 3 into the SubTable1.
This works as long as there is only
one MasterKey. But what if I want to create a 5 records for all (or a
potion) of the MasterKeys.
I could reexecute the command multiple times from a loop to get the results
I want, but I was curious if there was an easier way, using one SQL
Statement.
Thanks,
Tomtshad, it is not clear what you are trying to do. In the example you give
you would end up with 5 records in SubTable1 that all had MasterKey = 3 and
PK = 4, that doesn't seem to make much sense.
If you explain it better I can probably help. I think you may want to use an
IN list in the SELECT, so something like:
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK IN (4, 5, 6)
or otherwise you may need to use a subquery or a join, but I just can't tell
what you're trying to do.
Sean
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uIw5J5tjFHA.3756@.TK2MSFTNGP15.phx.gbl...
>I am trying to find an easier way to handle my insert/select statement. If
>I have the following tables and query - Is there a way to do this in one
>statement?
> create table Master(
> MasterKey int,
> )
> Create table SubTable1(
> MasterKey int,
> PK int,
> Data varChar(1000)
> )
>
> Create table SubTable2(
> MasterKey int,
> PK int,
> Data varChar(1000
> )
> Master Table
> MasterKey Priority
> 1 0
> 2 0
> 3 1
> 4 0
> 5 0
> 6 0
> SubTable1
> Empty
> SubTable2
> MasterKey PK
> 1 1
> 1 1
> 1 2
> 2 3
> 2 3
> 3 4
> 3 4
> 3 4
> 4 4
> 4 4
> 4 5
> 4 5
> What I want to be able to do is move data from SubTable2 to SubTable1
> I tried to do something like:
> Select @.MasterKey from Master where Priority = 1 (this would give
> me a MasterKey of 3)
> insert (MasterKey,PK,Data)
> Select @.MasterKey,PK,Data
> From SubTable2
> Where PK = 4
> This would move/create 5 records with a @.MasterKey of 3 into the
> SubTable1. This works as long as there is only
> one MasterKey. But what if I want to create a 5 records for all (or a
> potion) of the MasterKeys.
> I could reexecute the command multiple times from a loop to get the
> results I want, but I was curious if there was an easier way, using one
> SQL Statement.
> Thanks,
> Tom
>
>