insert/select

22 Jul 2005 4:56 PM

tshad

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

22 Jul 2005 6:46 PM

Sean Nolan

tshad, 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

Show quote

"tshad" <tscheider***@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
>
>
>