Stored Procedure, can you help me?
Below is my stored procedure. I want to use a stored procedure to select the entire date string from tbl_member and insert 2 tables. But that won't work. Can anyone help me?
Create PROCEDURE sp_test
AS
BEGIN
SET NOCOUNT ON;
Declare @A Varchar(255), @B Varchar(255), @C Varchar(255), @D int
Declare Table_Cursor Cursor
For select A, B, C from tbl_Member Open Table_Cursor
Fetch Next From Table_Cursor
Into @A, @B, @C While(@@Fetch_Status=0)
Begin Exec(
'insert into NewMember (A, B, C, D) values (@A, @B, @C, @D)
set @D = @@IDENTITY
Insert into MemberId (Mid) VALUES(@D)
)
Fetch Next From Table_Cursor Into @A, @B, @C End Close Table_Cursor
Deallocate Table_Cursor
END
GO
source to share
The first thing I see here is that you use the cursor when you don't need it. You can rewrite the first query as:
INSERT INTO NewMember(A, B, C, D)
SELECT A, B, C, D
FROM tbl_member
Then I would have an INSERT trigger against NewMember that inserted the ID column.
create trigger myInsertTrigger
on newmember
for insert
as
insert into memberid(mid)
select <<identity_column>> from inserted
By the way - it's a bad idea to use @@ IDENTITY to get the identity of the insert. Use the SCOPE_IDENTITY function instead.
source to share
Please pay special attention to what Pete said about @@. The reason it is bad to ever use @@ identity is because if a trigger is ever added to a table that inserts into another table with an identity, it is the identity that is returned not the one you just inserted. This can lead to serious data integrity issues that may not occur for several months.
Even if you saved the cursor (which I don't recommend, cursors are a very bad way to do inserts as they are very slow compared to the installed solution Pete gave) your code could not do the insert the first table if column D is an identity column (which I think will be, since you will later try to assign D to an id value). This is because you are trying to put a value in a column that cannot accept a value as it auto-generates. If D is an identity, then don't use it in insertion at all, just define the columns you need to insert. This would be true for Pete's solution as well, if D is indeed your identity column.
source to share