an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTOMyTable2 (MyId,MyName)

SELECT@.MyId,MyName

FROMMyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

you could write a view which uses a cursor to step through all the names and inserts the names and the result of that proc into a table, and then insert that.

What does the proc do ? Where does the ID come from that you need to use the proc ?

|||Please don't go the route of using cursors or writing procedural code. SQL is a set-based language and you will get the best performance if you utilize it. What does the SP GetMyID do? Why can't you use IDENTITY column on the table to generate automatic sequential numbers? This is a much more efficient mechanism. You can get the multiple ids generated by SQL Server using OUTPUT clause in SQL Server 2005 or via a trigger that dumps the rows from inserted table in SQL Server 2000 or requery base-table using the alternate key values from the rows that you inserted.|||

I would concur that a cursor is a last resort, but I was assuming there was a reason that the proc needs to be called on a line by line basis ( hence my questions about the nature of hte proc also ).

I was also assuming he needs to do a single insert, not that this code is going to be run regularly. b/c if the insert needs to happen on an ongoing basis, it should happen as each record is created.

|||

GetMyID Return an Id which is unique. This is SQL 2000, can you give me an example of trigger. I am just trying to get a new id for each row in my table.

|||

It generates an ID ? Ideally, I would have an identity column in the first table, and just insert the ID from the first table into the second ( so the string is only in your database once, and you can change it there to see it change throughout the database ).

A trigger is a proc that is fired when you perform a specific action such as an insert into a specific table. The help has lots of info on how they work.

|||

Hi cgraus,

That is true it is an id, however it is not identity, the way that the system is designed it creates a specific id. Is there any trigger example you can give me to accomplish to call the stored procedure for each row inserted into MyTable2 meaning accomplishes the following for each row.

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

|||

I guess you could do a trigger that runs when you insert a name in to MyTable2 ( I assume these names are contrived ), which calls the proc to generate the ID.

If an ID exists, why don't you store it in the table and use it throughout the system, regardless of where it comes from ?

http://www.codeproject.com/database/SquaredRomis.asp

Basically, the syntax is

CREATE TRIGGER invUpdate ON [Table2]
FOR INSERT

AS

...

Inside an insert trigger, the 'inserted' table will give you access to the items that were inserted. I am not sure if it's called once per line. If not, then a bulk insert leaves you with the same problem, but just within the trigger.