October 17, 2013 at 2:45 am
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 ..
October 17, 2013 at 3:18 am
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.
October 17, 2013 at 3:58 am
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;-)
October 17, 2013 at 4:57 am
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 .
October 17, 2013 at 5:03 am
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.
October 17, 2013 at 5:03 am
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;-)
October 17, 2013 at 5:04 am
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;-)
October 17, 2013 at 5:35 am
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.
October 17, 2013 at 5:35 am
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?
October 17, 2013 at 5:48 am
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
October 17, 2013 at 5:54 am
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 .. ??
October 17, 2013 at 6:05 am
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)
October 17, 2013 at 6:11 am
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
October 17, 2013 at 6:21 am
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)
October 17, 2013 at 6:27 am
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