Complete mind bender - Get Sql to execute its own

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complete mind bender - Get Sql to execute its own

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-06-27 : 21:39:42

I have this query:

Select 'Insert into NB_Store_ProductCategory values (' + CAST(p.ProductID as varchar) + ',' + CAST(c.CategoryID as varchar) + ')'
from NB_Store_Products p
join NB_Store_ProductLang pl on p.ProductID=pl.ProductID
join ShopImportsTotal SI on p.ProductRef=SI.ProductID
join NB_Store_CategoryLang CL on SI.Primary_Category=CL.CategoryName
join NB_Store_Categories C on CL.CategoryID=C.CategoryID
WHERE ModifiedDate > DATEADD(day, -5, GETDATE())
go

THis query will output what is a query that can be run which would look like this:

Insert into NB_Store_ProductCategory values (1227,1)
Insert into NB_Store_ProductCategory values (1228,1)
Insert into NB_Store_ProductCategory values (1229,1)
Insert into NB_Store_ProductCategory values (1230,1)

As you can see is a simple insert statement. How can I get SQL server after running the first query to execute on its own output. This needs to be schedule as a job so I can do a stored proc, or just TSQL and run it at intervals, any why at all that can be scripted is fine, but it just can't be manual.

I need to do this because say I was to simple add an ' Insert into NB_Store_ProductCategory (ProductID,CategoryID)' to the top of the first query it will complain at me because it has some triggers that will not let me do this, it sees it as a subquery...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-27 : 22:04:03

Just schedule the following query


Insert into NB_Store_ProductCategory 
SELECT	p.ProductID , c.CategoryID
from NB_Store_Products p
join NB_Store_ProductLang pl on p.ProductID=pl.ProductID
join ShopImportsTotal SI on p.ProductRef=SI.ProductID
join NB_Store_CategoryLang CL on SI.Primary_Category=CL.CategoryName
join NB_Store_Categories C on CL.CategoryID=C.CategoryID
WHERE ModifiedDate > DATEADD(day, -5, GETDATE())

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources