Insert records without loop - 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

Transact-SQL (2000)

Insert records without loop

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2004-09-14 : 11:17:39

I have a tblA.Id, tblA.Tot and I want to insert records into tblB without using a FOR or WHILE and fetch. Can this be done?

The number of inserted records for each Id should equal the tblA.Tot. For example.
tblA
Id Tot
1 2
2 3

tblB
Id
1
1
2
2
2

Thanks,
vmon

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 12:52:21

Why?

Brett

8-)

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-09-14 : 13:31:13

quote:
Originally posted by X002548

Why?

Lots of time and disk that they desperately need to use up?

-PatP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 13:52:39

if you have a table of numbers, this is really easy. but i'd have to ask why you are doing this as well.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 14:20:46

ok...so I was bored...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Numbers(n int NOT NULL)
GO

DECLARE @x int SELECT @x = 1 
WHILE @x < 1000
  BEGIN
	INSERT INTO Numbers(n) SELECT @x
	SET @x = @x + 1
  END

CREATE TABLE tblA([Id] int, Tot int)
INSERT INTO tblA([id],Tot) SELECT 1,2 UNION ALL SELECT 2,3

    SELECT a.[Id], a.Tot 
      FROM tblA a 
INNER JOIN Numbers b 
	ON a.Tot >= b.n
GO

SET NOCOUNT OFF
DROP TABLE tblA
DROP TABLE Numbers
GO

Brett

8-)

vmon
Yak Posting Veteran

63 Posts

Posted - 2004-09-14 : 14:25:16

I have an order for a quantity of 5 and I want to print a label for each.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 14:31:08

Brett has given you the basic idea, you just need a permanent table of Numbers in your database somewhere, just a table of Integers from 1 to whatever, about 1,000 is usually good.

Then just use a SELECT statement like the one he's posted.

But don't put this data into a table; just leave it is a VIEW or a simple SELECT statement or stored procedure that returns the data, and use that as the basis for the output of your labels.

It was the "Putting these records into a table" part that made us all wonder what you are trying to do.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources