Filling Table with Seq. Values from another Table

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)

Filling Table with Seq. Values from another Table

Author

Topic

PhillyBlunt
Starting Member

2 Posts

Posted - 2005-06-02 : 17:39:55

I have 2 tables.

Table 1
User Index (Identity)
PIndex

Table 2
PIndex
Name

I want to fill the PIndex column in Table 1 with values from Table 2.

Table 2 could have 3 records, 5, 10, ... whatever

I want Table 1 PIndex to have the sequential values 1, 2, 3, to whatever the max number in Table 2 is. Once it hits the max number, I want it to start over again at 1.

Ex:

Table 2

1 AAA
2 BBB
3 CCC
4 DDD
5 EEE

Table 1 would look like this

1 1
2 2
3 3
4 4
5 5
6 1
7 2
8 3
9 4
10 5
11 1

And so on.

Any ideas on how to do this would be greatly appreciated.

Thanks

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-02 : 18:15:10

quote:
Originally posted by PhillyBlunt

I want Table 1 PIndex to have the sequential values 1, 2, 3, to whatever the max number in Table 2 is. Once it hits the max number, I want it to start over again at 1.

When do you want it to stop?

This doesn't sound hard, but I'm foggy on the goals of this which makes implementation difficult.

If there are 10 rows in Table 2, what's the point of having more than 10 rows in Table 1? I guess this is the same question as "When do you want it to stop?"

INSERT INTO Table1(Pindex)
SELECT PIndex FROM Table2

That's one insertion.

Rinse and repeat as needed.

PhillyBlunt
Starting Member

2 Posts

Posted - 2005-06-02 : 18:32:57

Thanks for the reply.

Table 2 is like a promotions table that at any given time could have 5 promotions, 10, 15 ...

Table 1 is a list of customers. Every Customer will get ONLY 1 promotion from the table. I don't want the customers to get the same promotions. I want it to cycle through the promotions in Table 2.

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-02 : 20:45:56

So, it sounds like you would like to insert a promotion into Table1 from Table2 that is not already present in Table1?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-02 : 20:56:08

Look up CROSS JOIN, NOT EXISTS AND INSERT..SELECT

DavidM

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources