Trouble writing a query - 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)

Trouble writing a query

Author

Topic

sissimalek
Starting Member

3 Posts

Posted - 2002-01-14 : 15:31:07

Hi, I am having trouble writing a query.
I have to read data from one table and insert into another table and generate primary key (MAX (MASTERID) + 1) on the second table and in the same time update the primary key on 3 other tables. This should be done for each record of the first table. How Can I do that?
Thanks,
--
Sissi

Sissi Malek

sica
Posting Yak Master

143 Posts

Posted - 2002-01-14 : 16:23:02

Hi,

Have you tried with a trigger?
But anyway I'm a unsure you can update in the same time 3 tables...

Sica

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-14 : 17:00:22

Found this:

http://www.sqlteam.com/item.asp?ItemID=765

You could modify it to use the Max() number as a starting value, then have it auto-increment. This can be placed in a trigger and it should run fine. I would recommend that the incrementing column default to null or some fixed value (for the new rows), and that the UPDATE statement have a WHERE clause to update only those rows.

sissimalek
Starting Member

3 Posts

Posted - 2002-01-15 : 17:02:26

Thanks Rob. In that case Shall I just do the below:
declare @intCounter int
set @intCounter = MAX (table.MASTERID)
update table
SET @intCounter = tableSequenceNumber = @intCounter + 1
insert into table(masterid) tableSequenceNumber group by table.mastered;

Would you please clarify this by an example? thanks.

Sissi Malek

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-16 : 01:40:07

Sissi, does your MASTERID have to be sequential.. I'm asking this because eventually you will reach the limit of the type you are working with. You should look into NEWID() in conjunction with @@IDENTITY. And definitely work it in with triggers.

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

sissimalek
Starting Member

3 Posts

Posted - 2002-01-16 : 10:01:51

Thanks Jak. But Yes the numbering should be sequential and is Ok we won't go out of them.

Sissi Malek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources