createing new items based off a select?

I know this has to be possible with out using a cursor to loop through
this..
say I have tables like this...
Table A
==========
ItemID INT
Item TEXT
Table B
==========
PersonID int
ItemID int (from table A)
Table C
============
PersonID
Item
ItemID
Description
I want to do a select on table A get all items with the Item ID the person
in Table B hase and insert the result into Table C.
So if I have 2 items in A, and my Info in B, I want to do a query and have
records for each of them inserted into C with their info where it matches
together... I could easily do this with a cursor by looping through table A
looking for the ItemID of the current person then doing an Insert into table
C with the persons item information and the persons info... is there a way
to do this WITOUT a cursor and just a query? thanks!You're thinking in procedural language terms.
In T-SQL, it would go something like this...
insert into tablec (personid, item, itemid, description)
select b.personid, a.item, a.itemid, null
from tablea a
join tableb b on (a.itemid = b.itemid)
No idea where description is coming from so I nulled it.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into
table
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. If you had followed minimal netiquette, would your
pseudo-code look like this?
CREATE TABLE Items
(item _id INTEGER NOT NULL PRIMARY KEY,
Item_descrp VARCHAR(100) NOT NULL);
An item is not an attribute of a person; it is an entity, so we need to
fix your design.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
. );
CREATE TABLE Purchases
(person_id INTEGER NOT NULL
REFERENCES People(person_id),
item_id INTEGER NOT NULL
REFERENCES Items(item_id),
PRIMARY KEY (person_id, item_id));
and have records [sic] for each of them inserted into C with their info
where it matches together... I could easily do this with a cursor by
looping through table A looking for the ItemID of the current person
then doing an Insert into table C with the persons item information and
the persons info... is there a way to do this WITOUT a cursor and just
a query <<
Your tables are not normalized. Rows are not records; no wonder you
think of procedural code and cursors instead of a query. Do not
materialize a new table, as if you were allocating a scratch tape in a
file system.
CREATE VIEW PurchaseReport (..)
AS
SELECT P.*, B.*
FROM Purchases AS P, People AS B, Items AS I
WHERE I.item_id = P.item_id
AND B.person_id = P.person_id;
The VIEW will always be current, unlike a new, redundant base table.|||Try,
insert into tablec (personid, item, itemid)
select b.personid, a.item, a.itemid
from tableb as b inner join tablea as a
on b.itemid = a.itemid
AMB
"Brian Henry" wrote:

> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into tab
le
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>
>|||thats what I was trying to remember right there.. thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uGYGQc%23WFHA.2420@.TK2MSFTNGP12.phx.gbl...
> You're thinking in procedural language terms.
> In T-SQL, it would go something like this...
> insert into tablec (personid, item, itemid, description)
> select b.personid, a.item, a.itemid, null
> from tablea a
> join tableb b on (a.itemid = b.itemid)
> No idea where description is coming from so I nulled it.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> A
> table
>