Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to store multiple rows in a stored procedure's parameters and then insert them in a table Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
I have a query which gets multiple rows in result .
i want to convert it in a stored procedure so the values of each column get stored in a parameter and then insert those values in a table.
Any ideas to achieve this .
I have written the stored procedure but it picks up only one row from the result set of query and fills the parameter s with values and inserts them in the table... but it reads only one row, the first one.
How can i get able to read all the rows and insert them in table , with the help of some loop or whatever ... plzz help ..
Post #1505535
Posted Thursday, October 17, 2013 3:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
aeri_q (10/17/2013)
I have a query which gets multiple rows in result .
i want to convert it in a stored procedure so the values of each column get stored in a parameter and then insert those values in a table.
Any ideas to achieve this .
I have written the stored procedure but it picks up only one row from the result set of query and fills the parameter s with values and inserts them in the table... but it reads only one row, the first one.
How can i get able to read all the rows and insert them in table , with the help of some loop or whatever ... plzz help ..


You are not being that clear here.

Are you trying to pass a table to a Stored Procedure and then insert it's contents into a table within the Stored Procedure?
If that is the case you can use Table Valued Parameters.
Post #1505549
Posted Thursday, October 17, 2013 3:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
Here i dont think you need to use any parapmeter with Stored Proc.

see below code

create proc  p
as
select 1 union select 2


declare @t table (id int )



insert into @t exec p


select * from @t




-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1505566
Posted Thursday, October 17, 2013 4:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
No i dont want to pass table ..
Below is the query :
SELECT obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

where OBJECT_NAME(referenced_object_id) = 'My_table_name'

this query returns multiple rows .
Now i want a stored procedure which could read each row and insert it into a table .
Post #1505593
Posted Thursday, October 17, 2013 5:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
Using parameters works fine in this case .
i have written a stored proc which stores the results of this query in parameters, and then inserts these values in a table . But it does that only for the first row returned by the query.
I want to repeat this process for all the rows . As i want to store all rows returned by this query in a specific table.

Post #1505599
Posted Thursday, October 17, 2013 5:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
aeri_q (10/17/2013)
Now i want a stored procedure which could read each row and insert it into a table .
Any specific reason ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1505600
Posted Thursday, October 17, 2013 5:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
aeri_q (10/17/2013)
but it reads only one row, the first one.
How can i get able to read all the rows and insert them in table , with the help of some loop or whatever ... plzz help ..
Hang on ... your this (first post) is contradicting your last post ..


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1505601
Posted Thursday, October 17, 2013 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
Nothing is contradicting Bhuvnesh.
I just want to store the results in a table and for that i thought stored procedure would work best .
I want the proc to read each row and then insert it in table .. whats seems contradicting in it ....??? :O
My current procedure only reads the first row from the resultset of this query.
Hope u understand the case now.
Post #1505626
Posted Thursday, October 17, 2013 5:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
aeri_q (10/17/2013)
No i dont want to pass table ..
Below is the query :
SELECT obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

where OBJECT_NAME(referenced_object_id) = 'My_table_name'

this query returns multiple rows .
Now i want a stored procedure which could read each row and insert it into a table .


As I have already stated, I don't think you are being clear here.

Why would you assign the output of the query to parameters before you go on to insert it into a table.
Why do it row by row?

If the query you have specified is within the Stored Procedure it is a simple insert.
If it is being supplied to the Procedure then you can use a Table Valued Parameter to send it all in in one hit.

Unless...

there is a specific reason why you are inserting the data row by row, in which case, I would suggest you post all the code, including the Procedure you wrote to consume the rows so we can may be see if what you are attempting to do?
Post #1505627
Posted Thursday, October 17, 2013 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
Following procedure stores the value of each column in a parameter and then inserts in the table.
CREATE PROCEDURE DBO.FKEYS_FOR_KEYTBL
@FKNAME VARCHAR(100) = NULL,
@SCHNAME VARCHAR(5) = NULL,
@FKFLDNAME VARCHAR(20) = NULL,
@FKTBLNAME VARCHAR(10) = NULL,
@PKFLDNAME VARCHAR(20) = NULL,
@PKTBLNAME VARCHAR(10) = NULL,
@KEYTBLNAME VARCHAR(10) = NULL

AS
BEGIN

SELECT @FKNAME= obj.name ,
@SCHNAME= sch.name ,
@FKTBLNAME= tab1.name ,
@FKFLDNAME= col1.name ,
@PKTBLNAME= tab2.name ,
@PKFLDNAME= col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

where OBJECT_NAME(referenced_object_id) = @KEYTBLNAME

INSERT INTO SY.DB03F(FKNAME,SCHM,FKFLD,FKTBL,PKFLD,PKTBL)
VALUES(
@FKNAME,
@SCHNAME,
@FKFLDNAME,
@FKTBLNAME,
@PKFLDNAME,
@PKTBLNAME)

END

RETURN
GO
Post #1505632
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse