Square brackets defines the table simbs and then the columns En and ID, round brackets defines the enumeration of columns to be initiated and then the values for the columns, in my case one column and one value. In pure SQL the main statement kools like: INSERT INTO () OUTPUT INSERTED. I was looking for something like this for ages, with this detailed break down, I hope this helps. You can actually grab what ever you want in the 'OUTPUT inserted.WhatEverFieldNameYouWant' line and create what fields you want in your tempory table and access it to use how ever you want. Since there is only one record in this tempory table, and only one field, it will only select the ID number you need and drop it into ' ' now has the ID number you want and you can use it how you want like i have used it below. VALUES = (SELECT tempID FROM The line above is used to search the tempory table we created earlier where the ID we need is saved. This is then dropped into the tempory table we created earlier. So make sure it says the correct field name for your table, mine is 'comp_id'. OUTPUT p_id INTO The ' OUTPUT inserted.' line above is used to grab data out of any field in the record it is creating right now. It has only one field 'tempID' and its type INT is the same as the INSERT INTO comp(comp_name, comp_regno, comp_email) The line above is used to create a tempory table to hold the auto generated ID number for later use. The ' is used to hold the Company auto generated ID number that we are about to grab AS = CREATE PROCEDURE varchar(55) = 'Big nchar(8) = nvarchar(50) = INT OUTPUT It works on my SQL 2008 DB, should work on SQL 2005 and above. This creates a record in the company table (comp), it the grabs the auto ID created on the company table and drops that into a Staff table (staff) so the 2 tables can be linked, MANY staff to ONE company. I had the same need and found this answer.
0 Comments
Leave a Reply. |
AuthorAmy ArchivesCategories |