August 7, 2012 at 11:31 am
Hi,
I'm really new to DTSX packages and what I think I need to use dynamic SQL query/stored procedure. I can't seem to find a document that puts it all together for me and I can't piece together information due to my unfamiliarity with the aforementioned. So the project that I'm working is below:
-- One table in my very complex query is updated monthly with month ending date, for example, tblfactacts20120831. The table name changes monthly as well as the data.
-- I want to be able to select the most recent table using a query so I don't have to update each table names monthly. So, I think using dynamic SQL (sp_executesql) will get me there. By using the below query, it's pulling the correct table:
DECLARE @execquery AS NVARCHAR(MAX)
DECLARE @tablename AS NVARCHAR(128)
SET @tablename = (select top 1 name
from sys.tables
where name like 'tblFactAct2012%'
order by name desc)
select @execquery = N'Select * from dbo.' + quotename (@tablename)
EXECUTE sp_executesql @execquery
This is where I am stumped. After I execute the above SP, I don't know how to recall it into my original query which looks like the following:
select actdateid as Evt_Date
,dd.channel
,fd.ID
from @tablename fd
right join dbo.tblDP rp on fd.DPid= rp.DPid
right join dbo.tblAST st on st.ASTid= fd.ASTid
right join dbo.tblCC cc on cc.CCid= fd.CCid
right join dbo.tbl PS ps on ps.PSid = fd.PSid
right join dbo.tblDD dd on dd.DDid= fd.DDid
where rp.Desc not in ('xyz', 'abc')
and rp.GTD not in ('efg')
and st.AST not in (24,25,28,29)
and cc.CC not in(15, 19, 20, 25, 29, 34, 36)
and ps.PS in (2,3)
and fd.Ex = 1 and dd.channel in (
'dogs','cats','birds','fish') order by ID
Once I can incorporate it in my original query, I want to be able to create a DTSX package and transfer the data to another server. After it's transferred, I will call the data using VBA in Excel workbook.
Does anyone have any ideas, suggestions, resources for me to look at to complete this task. In summary, I want to incorporate the sp_executesql in my original query and then create a DTSX package.
Thank you in advance for your time!
August 7, 2012 at 3:48 pm
You can't execute a query against a variable like that.
select @execquery = N'Select * from dbo.' + quotename (@tablename)
EXECUTE sp_executesql @execquery
This is where I am stumped. After I execute the above SP, I don't know how to recall it into my original query which looks like the following:
select actdateid as Evt_Date
,dd.channel
,fd.ID
from @tablename fd
right join dbo.tblDP rp on fd.DPid= rp.DPid
What you could do is use a temp table. You would need to declare the temp table and use your dynamic sql to insert into it. Then your select would be from #YourTempTable.
FWIW, you should try to avoid select * whenever possible. Your code will break as soon as the table structure changes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply