Multiple inserts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple inserts

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2002-11-20 : 13:54:12

I tried searching the archives because I'm sure I've seen something about this before, but couldn't find anything.

Say I have table Inventory containing column InventoryType. I also have a purchase order form allowing the user to receive quantity X of Inventory.

I want to create X new (identical) rows in the Inventory Table.

As an unfortunate added twist, I can't use any SQL that is MS-SQL specific. Is this possible?

Thanks

1fred
Posting Yak Master

158 Posts

Posted - 2002-11-20 : 14:14:47

Try this, I don't think it is Ms-SQL based query

insert into Table
select * from Table

Phibian
Starting Member

32 Posts

Posted - 2002-11-20 : 14:25:08

No, that's not MS specific, but as far as I know it won't do what I want, because there is no guarantee that the number of rows in Table will be the same as x.

Ie. Say I want to insert 3 rows into table. There are already 20 rows in table.

In this case, insert into table Select * from table will insert 20 rows and not three.

Another example. Say I want to insert 10,000 rows into table. But there are currently only 100 rows.

Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-20 : 14:36:24

I hesitate to respond.*

Have you considered using a tally table?


insert into Inventory (InventoryType)
select 'X'
from toolbox.dbo.numbers n
where n.n <= 3

* What are you trying to do? What's your pk on Inventory? Why not store the quantity as a value rather than a duplicated row?

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-20 : 16:51:52

quote:
I also have a purchase order form allowing the user to receive quantity X of Inventory

Shouldn't this be adding to purchased table and subtracting from inventory.

I agree with Page47 the only reason to add each item separately instead of quantity would be to track returns for example.

Phibian
Starting Member

32 Posts

Posted - 2002-11-20 : 17:15:56

pk is InventoryID.

The reason for creating new items instead of storing quantity is that there are a few more columns in Inventory (eg SerialNumber) which mean that each item will end up being different (thus requiring a different row).

I was planning to have qty for non-serialized items, but go ahead and create each item for the items that are all different, and the tally table idea looks like it would work for that (thanks!)

Thinking about my approach however, I'm thinking I might get the user to enter the serial numbers right off the bat, in which case my SQL statements will all be different and it's a moot point.

Cheers!

Phibian
Starting Member

32 Posts

Posted - 2002-11-20 : 17:19:33

quote:

Shouldn't this be adding to purchased table and subtracting from inventory.

Thought I'd clarify this. Purchase Order is to order goods destined to go into inventory (not out of).

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-20 : 17:25:33

Pros:

Using tally table and identity field would save your users time.

Cons:

Without serial number users can enter in duplicate parts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources