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.
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.