code_junkie

How to get Identity value Column - SQL

Nov 13th, 2011
122
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.06 KB | None | 0 0
  1. Create Procedure prcInsertPrd
  2. As
  3. Begin
  4. Begin Transaction
  5. Declare @Identity int
  6. Insert into Temp_ProductsArchive (column1,column2,column3) select
  7. (column1,column2,column3)
  8. from Temp_Products
  9.  
  10. if(@@Error=0)
  11. Begin
  12. Commit
  13. End
  14. else
  15. Begin
  16. Rollback
  17. End
  18. End
  19.  
  20. Alter TRIGGER trgInsertTopProducts
  21. ON Temp_ProductsArchive
  22. AFTER INSERT
  23. AS
  24. BEGIN
  25. -- SET NOCOUNT ON added to prevent extra result sets from
  26. -- interfering with SELECT statements.
  27. SET NOCOUNT ON;
  28. declare @LeadId as int
  29. Select @LeadId=LeadId from inserted
  30. print @LeadId
  31.  
  32. Insert into Temp_ProductsTop(column4,LeadID,Column5,column6) Select column4,@LeadID 'LeadID',column 5,column6 from Temp_Products
  33. END
  34. GO
  35.  
  36. Alter TRIGGER trgInsertTopProducts
  37. ON Temp_ProductsArchive
  38. AFTER INSERT AS
  39. BEGIN
  40. SET NOCOUNT ON;
  41. insert into Temp_ProductsTop
  42. (ProductID,LeadID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)
  43. Select ProductID, inserted.LeadID, ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
  44. from
  45. Temp_Products
  46. inner join inserted on inserted.Leadid = ?
  47. END
  48.  
  49. GO
  50.  
  51. Alter TRIGGER trgInsertTopProducts
  52. ON Temp_ProductsArchive
  53. AFTER INSERT
  54. AS
  55. BEGIN
  56. -- SET NOCOUNT ON added to prevent extra result sets from
  57. -- interfering with SELECT statements.
  58. SET NOCOUNT ON;
  59. declare @LeadId as int
  60. declare c CURSOR READ_ONLY for
  61. select LeadId from inserted
  62.  
  63. open c
  64. fetch next from c into @LeadId
  65. while @@FETCH_STATUS = 0
  66. BEGIN
  67. Insert into Temp_ProductsTop(ProductID,LeadID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) Select ProductID,@LeadID 'LeadID',ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued from Temp_Products
  68. fetch next from c into @LeadId
  69. END
  70. close c
  71. deallocate c
  72. END
Add Comment
Please, Sign In to add comment