SP to record inputs plus random records from 2nd table

munklefish

Hi, I'm in need of a stored procedure which takes 5 input values and then stores them in 'Table A'. This is simple enough. However i also need to get data from a 2nd table 'Table B', return 5 random records(1 column only) and then input these records into 'Table A' along with the other data mentioned above. (Making a single record / row of 10 bits of information) Hopefully this makes sense. I know how to create the select statement for the above, but how do i then capture this data within the stored procedure and use it in the insert statement? Thanks! :doh:

Niladri_Biswas

Your question is not very clear, because a) You never specified if the data in TableA has been inserted already and then you want to update the 6th column of TableA [ As you specified that the first 5 fields of TableA will have values supplied from the Stored Proc which indicates that the 6th column will have values from TableB ] with the values from TableB. b) The Table Structure of both the tables are absent. c) What will be the ultimate output [ Any rough sketch will make the picture more clear] d) Whether Table2's column will have predefined value or not. If not whether the values needs to be inserted at runtime in TableB and then it has to be fetched and next to be inserted in TableA? e) Any dependency is lying between the two tables(e.g. Referential Integrity Constraint) However, I am assuming that TableA has the following structure TableA

Col1 Col2 Col3 Col4 Col5 Col6

All are Varchar(50) types. N.B.~ Col6 will have the value from TableB TableB

RandomCol

Again Varchar(50) And my Stored Proc is as under:

ALTER PROCEDURE InsertRecordsIntoTableA
-- Add the parameters for the stored procedure here
(
@VarCol1 AS VARCHAR(50),
@VarCol2 AS VARCHAR(50),
@VarCol3 AS VARCHAR(50),
@VarCol4 AS VARCHAR(50),
@VarCol5 AS VARCHAR(50)
)
AS
BEGIN

-- VARIABLE DECLARATION
DECLARE @RANDOMCOLUMNVALUES VARCHAR(50)

-- STEP 1: DECLARE A CURSOR
DECLARE MYRANDOMCURSOR CURSOR FOR

	SELECT    RANDOMCOL
	FROM      TABLEB

-- STEP 2: OPEN THE CURSOR
OPEN MYRANDOMCURSOR 

FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES

-- STEP 3: START THE LOGIC

WHILE @@Fetch\_Status = 0

BEGIN

	-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE A

		INSERT INTO TABLEA(COL1,COL2,COL3,COL4,COL5,**COL6**)

		VALUES(@VarCol1,@VarCol2,@VarCol3,@VarCol4,@VarCol5,**@RANDOMCOLUMNVALUES**)
	
-- STEP 5: GET THE NEXT RECORD
FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES

END

--STEP 6: CLOSE THE CURSOR
CLOSE MYRANDOMCURSOR

--STEP 6: DEALLOCATE THE CURSOR
DEALLOCATE MYRANDOMCURSOR

END

Hope this answers you question. If not, please tell your problem precisely with necessary description It can even be solved without using CURSOR and with a while loop Please be specific about your question so that others can understand properly. :)

Niladri B