Creating a flat table -- sql server new users

sql server new users : Creating a flat table

Ernie
5/17/2007 3:11:53 PM

I have a MSSQLSERVER2000 table 'FRUIT' with the following data

ITEM CODE QTY
Apple 101 2
Banana 102 1
Orange 103 3

What I need is to create a MSSQLSERVER2000 table 'FRUITFLAT' that
looks like this:

ITEM CODE QTY
Apple 101 1
Apple 101 1
Banana 102 1
Orange 103 1
Orange 103 1
Orange 103 1

Can I do this with a Stored Procedure or does it need to be done with
VB (For/Next)? I am using MSACCESS2003 as a front-end for the
database.

Either way, could you provide [specific and detailed] code that allows
me to do this.

Thanks.

Ernie

Tom Moreau
5/17/2007 6:36:49 PM

Something like:

while exists (select * from FRUIT where QTY > 1)
begin
begin tran
insert FRUIT (ITEM, CODE, QTY)
select
ITEM, CODE, 1
from
FRUIT
where
QTY > 1

update FRUIT
set
QTY = QTY - 1
where
QTY > 1
commit tran
end

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

[quoted text, click to view]
I have a MSSQLSERVER2000 table 'FRUIT' with the following data

ITEM CODE QTY
Apple 101 2
Banana 102 1
Orange 103 3

What I need is to create a MSSQLSERVER2000 table 'FRUITFLAT' that
looks like this:

ITEM CODE QTY
Apple 101 1
Apple 101 1
Banana 102 1
Orange 103 1
Orange 103 1
Orange 103 1

Can I do this with a Stored Procedure or does it need to be done with
VB (For/Next)? I am using MSACCESS2003 as a front-end for the
database.

Either way, could you provide [specific and detailed] code that allows
me to do this.

Thanks.

Ernie

Ernie
5/18/2007 5:41:19 AM

[quoted text, click to view]

Thanks, Tom!

Is this code that I would use in an Update or Append or MakeTable
Stored Procedure?

Ernie

Tom Moreau
5/19/2007 11:39:04 AM

What this does is basically expand the data you have in the existing FRUITS
table. Re-reading your original post, I see that you want the stuff in a
new table, FRUITFLAT. So what I'd do is insert everything from FRUIT into
FRUITFLAT and then run my code against FRUITFLAT.

Now, to answer your latest question, I need a bit more info. Is FRUIT
static or are you updating it and want the changes reflected in FRUITFLAT?
If so, that can be managed via a trigger on FRUIT. The FRUITFLAT table
would have to exist before you place the trigger on FRUIT.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

[quoted text, click to view]

Thanks, Tom!

Is this code that I would use in an Update or Append or MakeTable
Stored Procedure?

Ernie

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn