• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Execute SQL query from a column value

Team - I have a table with 2 fields
Table Name: RunQuery
ReportID - nvarchar(max)
Formula -  nvarchar(max)

Ex:
123 | Select '123' as Customer, '111' as Org' from Table1
123 | Select '133' as Customer, '121' as Org' from Table1

What I need to do is..,

Lets say Count(Formula) from table "RunQuery" = 70

I need to insert into TableOutput(ReportID, Customer, Org) for every value in Formula cell as its a sql query.

Loop is what i need i guess.., Any sample query would be very helpful.
0
Manju
Asked:
Manju
  • 4
  • 2
1 Solution
 
Mark ElySenior Coldfusion DeveloperCommented:
INSERT INTO TableOutput
(ReportID, Customer, Org)
SELECT ReportID, Customer, Org
FROM RunQuery
0
 
ManjuIT - Project ManagerAuthor Commented:
Mark - Nope, what i meant was..,

RunQuery table gets created run time.., so count(*) of runquery vary from time to time, however the 2 columns in Runquery are
ReportID & Formula.

here Formula column will have SQL queries

for ex: if the record count of RunQuery is 75, Formula column will have 75 queries (1 query per row)

I need to run each result of Formula column against the insert command..,

Typically what im looking is..,

DECLARE @cnt INT = 1;
Declare @Rowcount int
Declare @Query nvarchar(max)

set @Rowcount = (Select COUNT(*) from Axon_RunQuery)
Set @Query = (Select RunQuery from Axon_RunQuery where ReportID = '1011')

WHILE @cnt <= @Rowcount
BEGIN
   Insert into Test_Output(Organization, Product, Customer, SU, GIV)
   @Query
   
   SET @cnt = @cnt + 1;
END;

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
btw the above query doesnt work as it says "Incorrect syntax near '@Query'."
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ManjuIT - Project ManagerAuthor Commented:
I just changed it @Query line to "Exec (@Query)".

It throws a new error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Try this.. I did not proof it.. let me know if it has errors.
DECLARE 
	@ReportID nvarchar(max),
	@Forumula nvarchar(max)


DECLARE db_cursor CURSOR FOR  
SELECT ReportID, CustomerID
FROM RunQuery
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO 
	@ReportID,
	@Forumula

WHILE @@FETCH_STATUS = 0  
BEGIN  
	
	
	IF NOT EXISTS (
	SELECT 1 
		FROM dbo.RunQuery AS R
		WHERE	R.ReportID = @ReportID AND
				R.Forumula = @Forumula
		)
			BEGIN
			  INSERT INTO dbo.TableOutput
            (ReportID, Formula)
             SELECT @ReportID, @Forumula
			
			END
   
       FETCH NEXT FROM db_cursor INTO 
   @ReportID,
	@Forumula
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
Mark - Nope, this is just saving the cell as it is. I'd want to run the query..,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you need is a SELECT ... INTO clause but the columns from SELECT need to match the columns of the table in INTO:
DECLARE @cnt INT = 1;
Declare @Rowcount int
Declare @Query nvarchar(max)

Select @Rowcount=COUNT(*) from Axon_RunQuery

WHILE @cnt <= @Rowcount
BEGIN
   Select RunQuery 
   into Test_Output
   from Axon_RunQuery where ReportID = '1011'
   
   SET @cnt = @cnt + 1;
END;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now