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 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
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 .. ??

Post #1505639
Posted Thursday, October 17, 2013 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
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)

Post #1505644
Posted Thursday, October 17, 2013 6:11 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)
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


Post #1505651
Posted Thursday, October 17, 2013 6:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
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)
Post #1505662
Posted Thursday, October 17, 2013 6:27 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 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?
Post #1505666
Posted Thursday, October 17, 2013 6:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #1505668
Posted Thursday, October 17, 2013 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
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 .

Post #1505676
Posted Thursday, October 17, 2013 6:49 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
Glad we could help.
Post #1505679
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse