asked on

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.

Mark Ely

INSERT INTO TableOutput
(ReportID, Customer, Org)
SELECT ReportID, Customer, Org
FROM RunQuery

Manju

ASKER

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

Manju

ASKER

btw the above query doesnt work as it says "Incorrect syntax near '@Query'."

Manju

ASKER

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.

Mark Ely

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

Manju

ASKER

Mark - Nope, this is just saving the cell as it is. I'd want to run the query..,

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial