October 29, 2013 at 9:01 am
Sorry if I posted this in the wrong location but nothing else seems to fit.
I would like to know if it's possible to run a tsql statement that would build multiple excel files by breaking on a column name change saved to a network share. For instance
State break so the excel out put would be al.xlsx, ks.xlsx, mo.xlsx
data would simply be
State, Fname, Lname --> Sorry no example of data just wondering if this is even possible. Basically running some sql query loop that would create a new file once the state name changed. SSIS is probably not an option because I would have to build out all of the states anyway, want something a little more dynamic.
October 29, 2013 at 10:40 am
Not sure how to do the Excel part. However, for a dynamic approach you could do something like this:
-- Create a table with the distinct values you're looking for
SELECT DISTINCT stateName INTO #tmpStates FROM myTable
-- Iterate over that list and call a proc or function to do the excel part for each state
DECLARE @vc nvarchar(100)
WHILE EXISTS (SELECT stateName from #tmpStates) BEGIN
SELECT @vc = TOP 1 stateName from #tmpStates
-- Pass to proc
EXECUTE myProcToCreateExcel(@vc)
-- Proc would select * from myTable where stateName = @vc
-- and do something with the data
-- Delete that one
DELETE #tmpStates WHERE stateName = @vc
END
October 30, 2013 at 11:18 am
I would look at exporting using OPENROWSET.
You could probably use SSIS to call a single package multiple times. Pass the state as a package variable. Use the variable to create a file and also as a parameter to retrieve the appropriate data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply