updating several tables

[koniba]

I have several tables where I want to add information to for a user interface which does not have the productid. So I want to capture the last productid and (1) to it for the next productid and insert that id info into three tables ( commnets, phase, phasedescription). Here is what i think will do the job for me. I declare a variable to capture the value of the last producti and increase it by 1.

Dim prod
prod = "SELECT(last_productid)+ 1 FROM Product

Then the insert statement for the other tables will be:

Dim strInsert
strInsert = INSERT INTO Comment (prod, nature, comments) VALUES (prod, suggestions, blah blah)

For table Description it will be:
strInsert = INSERT INTO Description(prod, phasename, steps, status) VALUES (prod, phasename, H3, 2).

And so on. However, for some reason it is not working.

Please tell what I should do now.

Thanks
koniba
niarela@aol.com

 

[CountRaven]

Koniba,

Although the method you are using will work, I would advise
you use an Identity Column in the database for your Product ID. This will automaticaly increase by whatever increment you specify when you insert a new record into that table.

Immediatly after you insert the record run the following query "SELECT @@INDENTITY", this will return your new Product ID for use in your other tables.

If you still wish to use your current method I need to know more information in order to determin the problem:

1) At which point did the error occure?
2) What error message did you recieve?

Hope this helps.

 

[koniba]

Thanks very much.

However, I tried yours and I got two errors messages

1) Variable must be declared first. I declared a variable And got:

2) "Invalid Character" for "@@" sign preceedint the "INDENTITY".

When I run my own statement on the sql query analyzer the error message was that "Last_productid" was not a valid column name. Yes only "productid" is the column name. It also said that "LAST" was not sql valid function name.

I am finaly lost.

Thanks

 

[1111111111]

create stored proc in SQL7.0

' ------------------------------------------- '
create proc Update_3_tables
as
declare @prod char(...) or int ''whatever you need

@prod = SELECT (last_productid)+ 1 FROM Product

INSERT INTO Comment (prod, nature, comments) VALUES (@prod, suggestions, blah blah)

INSERT INTO Description(prod, phasename, steps, status) VALUES (@prod, phasename, H3, 2).

and so on ....
But talk to your DBA, because if table locked system will
rollback

 

[koniba]

Thank you very much for responding so quickly to my question. I will try this right away.

Thank you again,
Koniba.

 

[Guest_imported]

Does anyone know how to load a table in SQL Server with the data from a table in Oracle? How do you link Oracle to a SQL Server?
Thanks!

 

[TomSark]

In SQL Server 7.0:

One way to do it would be to:

Set up a linked server in SQL Server that references the Oracle Database and then use DTS to load the table. Refer to SQL Server books-online for more info.

Tom