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

  • 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 ..

  • 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.

  • 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;-)

  • 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

    ,

    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 .

  • 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.

  • 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;-)

  • 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;-)

  • 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.

  • 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

    ,

    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?

  • 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 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 .. ??

  • 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)

  • 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

  • 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)

  • 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?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply