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 m doing it row by row, because i think its the only way to do it
I want to insert each row in my table which is returned by that query .
This query returns all the referencing key tables and columns for the table which is entererd in WHERE clause.

Is there any other way to insert the resultset of this query in 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
I am doing it row by row because i think its the only way to do this .
The query i have mentioned, if run directly in TSQL ..it returns 3 rows for a specific table of my database.
But when i wrote stored procedure using the same query it inserted the first row in my table. (The first of the 3 rows which where returned by the query,when executed alone)
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)
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


I think all you need here is just a simple INSERT... SELECT within the Stored Procedure with the @KEYTBLNAME as filter.

something along the lines of...


INSERT INTO SY.DB03F(FKNAME,SCHM,FKFLD,FKTBL,PKFLD,PKTBL)

SELECT obj.name ,
sch.name ,
tab1.name ,
col1.name ,
tab2.name ,
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



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 am doing it row by row because i think its the only way to do this .
The query i have mentioned, if run directly in TSQL ..it returns 3 rows for a specific table of my database.
But when i wrote stored procedure using the same query it inserted the first row in my table. (The first of the 3 rows which where returned by the query,when executed alone)
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 am doing it row by row because i think its the only way to do this .
The query i have mentioned, if run directly in TSQL ..it returns 3 rows for a specific table of my database.
But when i wrote stored procedure using the same query it inserted the first row in my table. (The first of the 3 rows which where returned by the query,when executed alone)


Assuming your plan is to insert all rows from your output into your table, does my earlier response not help?
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: 2928 Visits: 4076
aeri_q (10/17/2013)
it returns 3 rows for a specific table of my database.
But when i wrote stored procedure using the same query it inserted the first row in my table. (The first of the 3 rows which where returned by the query,when executed alone)
it will because .

you below query will basically executed ONE time during every Sp execution and fill all the variable of INSERT statement once.

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




Instead .... go with the query which OTF mentioned and use it in place of above SQL insert. .

-------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
Ohh thank you so much ,
It was just a little and silly mistake i was doing .
INSERT ..SELECT works fine in this case .
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
Glad we could help.
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