Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to store multiple rows in a stored procedure's parameters and then insert them in a table


how to store multiple rows in a stored procedure's parameters and then insert them in a table

Author
Message
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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 ..
OTF
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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;-)
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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 .
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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;-)
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
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 ..Pinch

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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.
OTF
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
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?
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search