question

If we assume you have a table `MyTable` eg. defined as CREATE TABLE MyTable ( MBR_ID INT , NAME VARCHAR(50) , BIRTHDATE DATE , DEPT VARCHAR(50) , ADDRESSS VARCHAR(128) , SAL MONEY , EMAIL_ID VARCHAR(128) ) Then you write your queries as: INSERT INTO MyTable(MBR_ID, NAME, BIRTHDATE) SELECT MBR_ID, NAME, BIRTHDATE FROM TABLE 1 INSERT INTO MyTable(MBR_ID, DEPT, ADDRESS, SAL, EMAIL_ID) SELECT MBR_ID, DEPT, ADDRESS, SAL, EMAIL_ID FROM TABLE 2 In case the selected columns are really dynamic, then you can have eg. one XML column in the table and store the dynamic data as XML, which can be queried when needed.

I'm not sure why you're trying to do this (it seems like a bad idea to me). But... Getting column headers from query sets is quite tricky. You also need to think about how you're going to make sure that results and headers don't get mixed up. This should get you started: create table #MyResults (RowId int identity, ResultGroup int, Result1 nvarchar(20), Result2 nvarchar(20), Result3 nvarchar(20), Result4 nvarchar(20)) go create table #MyTable1 (MBR_Id int, Dept nvarchar(20)) insert #MyTable1 values(1,'HR'),(2,'Payroll') go create table #MyTable2 (MBR_Id int, Email nvarchar(20)) insert #MyTable2 values(1, 'a@b.com'),(2, 'c@d.com') go -- get the column headers only ; with cte as ( select ColumnName , ColumnIndex = row_number() over (order by (select null)) from ( select top 1 MBR_Id = cast(MBR_Id as nvarchar(20)) , Dept from #MyTable1 ) src unpivot ( Redundant for ColumnName in (MBR_Id,Dept) ) upvt ) insert #MyResults (ResultGroup, Result1, Result2) select (select isnull(max(ResultGroup),0) from #MyResults) + 1 , max(case when ColumnIndex = 1 then ColumnName else null end) , max(case when ColumnIndex = 2 then ColumnName else null end) from cte -- then add data insert #MyResults (ResultGroup, Result1, Result2) select (select isnull(max(ResultGroup),0) from #MyResults) , MBR_Id , Dept from #MyTable1 -- show in order select * from #MyResults order by ResultGroup, RowId Note that the unpivot requires that all columns in the source query have the same data type. Also, you'll need to think about how you cast types in the data insert - my example relies on implicit conversions, but this won't hold for all data types.

I'd probably do this via script too, but here's a workable solution, given various assumptions that I've made based on what you've said in your comment to my previous answer: 1. You have a set of scripts where each script consists only of a single select query. 2. Each query contains a column with the SCRIPT_ID explicity set (based on "SCRIPT_ID which will be available for each rows". 3. You are running under an account that has permissions to create objects (specifically views). 4. You are running under an account that has permissions on sys.columns 5. You're ok with using undocumented features. So some fairly big assumptions. The steps involved are: 1. Get a list of files to process 2. For each file: 1. Get query text from file and pass into worktable 2. Create view from query text 3. Use sys.columns to get column names and insert 4. Insert data Hopefully this is helpful. I've attached my test files in a zip. use Sandbox go -- run in stages! --------------------- -- set up source data --------------------- create table dbo.MyTable1 (MBR_Id int, Dept nvarchar(20)) insert dbo.MyTable1 values(1,'HR'),(2,'Payroll') go create table dbo.MyTable2 (MBR_Id int, Email nvarchar(20)) insert dbo.MyTable2 values(1, 'a@b.com'),(2, 'c@d.com') go -------------------- -- set up worktables -------------------- create table dbo.MyResults (RowId int identity, Script_Id int, Result1 nvarchar(20), Result2 nvarchar(20), Result3 nvarchar(20), Result4 nvarchar(20)) go create table dbo.MyScriptStructure (RowId int identity, ScriptFullPath nvarchar(4000), ScriptName nvarchar(400), ScriptDepth smallint, IsFile bit, ParentDirectoryId int) go create table dbo.MyScriptQuery (ScriptText varchar(max)) go ---------------------------------- -- return list of files to process ---------------------------------- declare @root_folder nvarchar(400) = 'C:\Temp\SQLFiles' -- use xp_dirtree to list the files in the root folder -- note that it is undocumented and so is unsupported -- parameters: -- directory - root directory to search -- depth - how many subfolder levels to return, default 0 returns all subfolders -- file - choose to display files as well as folders, default 0 returns folders only, 1 returns files as well -- insert the structure info into our worktable insert dbo.MyScriptStructure (ScriptName,ScriptDepth,IsFile) exec xp_dirtree @root_folder, 0, 1 -- show result select * from dbo.MyScriptStructure /* RowId ScriptFullPath ScriptName ScriptDepth IsFile ParentDirectoryId 1 NULL Script_1.sql 1 1 NULL 2 NULL Script_2.sql 1 1 NULL 3 NULL Script_3.sql 1 1 NULL 4 NULL SQLFilesChild1 1 0 NULL 5 NULL Script_4.sql 2 1 NULL 6 NULL SQLFilesChild2 1 0 NULL 7 NULL Script_5.sql 2 1 NULL */ -- note that only the file or folder name is returned -- this means that we have to build the full path for each file -- first update the ParentDirectoryId for each row -- default to 0 for those items whose parent is the root update dbo.MyScriptStructure set ParentDirectoryId = isnull((select max(RowId) from dbo.MyScriptStructure where RowId < ss.RowId and ScriptDepth = ss.ScriptDepth - 1),0) from dbo.MyScriptStructure ss -- use a recursive cte to update the file paths ; with cte as ( select top 1 RowId = 0, FilePath = cast('C:\Temp\SQLFiles' as nvarchar(4000)) union all select recur.RowId, FilePath = anchor.FilePath + N'\' + recur.ScriptName from dbo.MyScriptStructure recur join cte anchor on recur.ParentDirectoryId = anchor.RowId ) update dbo.MyScriptStructure set ScriptFullPath = cte.FilePath from dbo.MyScriptStructure ss join cte on ss.RowId = cte.RowId -- show result select * from dbo.MyScriptStructure /* RowId ScriptFullPath ScriptName ScriptDepth IsFile ParentDirectoryId 1 C:\Temp\SQLFiles\Script_1.sql Script_1.sql 1 1 0 2 C:\Temp\SQLFiles\Script_2.sql Script_2.sql 1 1 0 3 C:\Temp\SQLFiles\Script_3.sql Script_3.sql 1 1 0 4 C:\Temp\SQLFiles\SQLFilesChild1 SQLFilesChild1 1 0 0 5 C:\Temp\SQLFiles\SQLFilesChild1\Script_4.sql Script_4.sql 2 1 4 6 C:\Temp\SQLFiles\SQLFilesChild2 SQLFilesChild2 1 0 0 7 C:\Temp\SQLFiles\SQLFilesChild2\Script_5.sql Script_5.sql 2 1 6 */ go ---------------------------------------------------------------------- -- loop through the file list, executing scripts and collating results ---------------------------------------------------------------------- -- clear any previous results truncate table dbo.MyResults -- open the file list in a cursor declare curFiles cursor for select ScriptFullPath from dbo.MyScriptStructure where IsFile = 1 open curFiles -- declare working variables declare @fetch int , @path nvarchar(4000) , @command nvarchar(4000) , @query_text nvarchar(max) , @view_statement nvarchar(max) , @script_id int , @insert_cols nvarchar(4000) , @select_cols nvarchar(4000) , @insert_statement nvarchar(4000) -- get the first cursor row fetch next from curFiles into @path set @fetch = @@fetch_status -- loop through the file list while @fetch = 0 begin -- clear everything truncate table dbo.MyScriptQuery set @insert_cols = '' set @select_cols = '' -------------------------------------------------------------- -- use bulk insert to get the file contents into our worktable -------------------------------------------------------------- -- note use of nonsensical field terminator and blank row terminator -- this makes sure that all data goes into a single row select @command = 'bulk insert dbo.MyScriptQuery from' + ' ''' + replace(@path, '"', '') + '''' + ' with (fieldterminator=''$$$$$$$''' + ',rowterminator = ''''' + ')' -- typical output -- bulk insert Sandbox.dbo.MyScriptQuery from 'C:\Temp\SQLFiles\Script_1.sql' with (fieldterminator='$$$$$$$',rowterminator = '') exec (@command) -- return the query text to a variable select @query_text = ScriptText from dbo.MyScriptQuery ----------------------------------- -- create a view based on the query ----------------------------------- -- drop any existing version first if isnull(object_id('dbo.TemporaryScriptView'),0) <> 0 drop view dbo.TemporaryScriptView -- build and execute statement select @view_statement = 'create view dbo.TemporaryScriptView as ' + @query_text exec (@view_statement) -- because we've built the view, we now have access to the column collection via sys.columns -- return the script_id by querying the view select top 1 @script_id = Script_Id from dbo.TemporaryScriptView ----------------------------------- -- add headers to the results table ----------------------------------- -- note reliance on undocumented variable based concatenation -- build and execute an insert statement -- get the set of columns to insert select @insert_cols = @insert_cols + 'Result' + cast(column_id as nvarchar) + ',' from sys.columns where object_id = object_id('dbo.TemporaryScriptView') order by column_id -- get the set of column names to select select @select_cols = @select_cols + '''' + name + ''',' from sys.columns where object_id = object_id('dbo.TemporaryScriptView') order by column_id -- build the statement to insert the headers select @insert_statement = 'insert dbo.MyResults (Script_Id,' + left(@insert_cols,len(@insert_cols)-1) + ') ' + 'select ''' + cast(@script_id as nvarchar) + ''',' + left(@select_cols,len(@select_cols)-1) -- typical output -- insert dbo.MyResults (Script_Id,Result1,Result2,Result3) select '1','Script_ID','MBR_Id','Dept' -- execute exec (@insert_statement) ------------------------------------ -- add the data to the results table ------------------------------------ -- clear column list set @select_cols = '' -- get the set of columns to select -- note explicit cast to target data type select @select_cols = @select_cols + 'cast([' + name + '] as nvarchar(20)),' from sys.columns where object_id = object_id('dbo.TemporaryScriptView') order by column_id -- build the statement to insert the data select @insert_statement = 'insert dbo.MyResults (Script_Id,' + left(@insert_cols,len(@insert_cols)-1) + ') ' + 'select ''' + cast(@script_id as nvarchar) + ''',' + left(@select_cols,len(@select_cols)-1) + ' from dbo.TemporaryScriptView' -- typical output -- insert dbo.MyResults (Script_Id,Result1,Result2,Result3) select '1',cast([Script_ID] as nvarchar(20)),cast([MBR_Id] as nvarchar(20)),cast([Dept] as nvarchar(20)) from dbo.TemporaryScriptView -- execute exec (@insert_statement) -------------------------- -- get the next cursor row -------------------------- fetch next from curFiles into @path set @fetch = @@fetch_status end -- tidy up close curFiles deallocate curFiles -- show results select * from dbo.MyResults order by RowId /* RowId Script_Id Result1 Result2 Result3 Result4 ----------- ----------- -------------------- -------------------- -------------------- -------------------- 1 1 Script_ID MBR_Id Dept NULL 2 1 1 1 HR NULL 3 1 1 2 Payroll NULL 4 2 Script_ID MBR_Id Email NULL 5 2 2 1 a@b.com NULL 6 2 2 2 c@d.com NULL 7 3 Script_ID MBR_Id Dept Email 8 3 3 1 HR a@b.com 9 3 3 2 Payroll c@d.com 10 4 Script_ID MBR_Id Dept Email 11 4 4 1 HR a@b.com 12 4 4 2 Payroll c@d.com 13 5 Script_ID MBR_Id Dept Email 14 5 5 1 HR a@b.com 15 5 5 2 Payroll c@d.com */ [link text][1] [1]: /storage/temp/2146-sqlfiles.zip