Cfrgtkky

I will not go into the reasons for this requirement (too long and not relevant).

I have a rather wide table Table_1 with 256 columns, all NVARCHAR(512) and accepting null.

On the other hand, I have a string into which a dynamic select is built and, depending on the case, it generates each time a different number of columns.

The results of the query need to be inserted into the Table_1.

The problem is that either I explicitly mention the columns into which results are to be inserted or the results of the query must match the structure of the target table.

For instance, let's have:

DECLARE @Table_1 TABLE (Field_1 NVARCHAR(512) ,

                        Field_2 NVARCHAR(512) ,

                        Field_3 NVARCHAR(512) ,

                        Field_4 NVARCHAR(512) ,

                        Field_5 NVARCHAR(512) ,

                        Field_6 NVARCHAR(512)   ) ;

And a query like:

DECLARE @_My_Query NVARCHAR(1000) = '

SELECT Name    Field_1 ,

       Street  Field_2 ,

       Phone   Field_3

 FROM My_Table 

 ';

Now, I can't do:

INSERT INTO @Table_1 

  EXECUTE sp_executesql @_My_Query ;

because it would complain about mismatching columns. Neither can I hardcode (Field_1,Field_2,Field_3) because other queries would need a different number of columns.

Edit

Answers to the comments posted by KrazzyNefarious, Jeroen Mostert and Damien_The_Unbeliever:

I do have a clear view of the fields returned by the query as well as their count.

The functionality I'm trying to implement is as follows:

1. The system I'm working on should support the generation of CSV files with a variable number of columns and contents.




2. The idea is that the query that generates the result data is prepared at a higher layer and sent to a Stored Procedure (as a string) along with a table that describes all the columns of the result (e.g. ordinal position, field name, data type, title in the Excel's first row, etc.).




3. The Procedure I'm working on would take the query string and run a combined INSERT INTO Table_1 EXECUTE sp_executesql @_My_Query. Once this is done, the mechanism that examines each and every field (for instance, to add " whenever needed) can handle the contents of Table_1 without any concern about the origin of the data.

I would prefer to avoid using temporary tables since, besides the ugliness of the approach, it affects performance and additional issues (need to delete records after use, collisions, etc.).

This:

The system I'm working on should support the generation of CSV files with a variable number of columns and contents.

is the crux of your problem. You have chosen a path that doesn't really match/support this requirement. You have ruled out temp tables. So the only other option is to create a (temp or permanent) table for each export "situation" - which is ugly and prone to lots of other issues. Because a table in a rdbms has a fixed structure. More than just a different number of columns, you also need to consider different datatypes. Are you ignoring that issue as well or did you make a very simplifying assumption? If you only look at one situation you often lose sight of the complexity you face.

In addition, you can search for the many discussions about exporting CSV data from a stored procedure (or script). It is simply a nest of problems and constraints even without the variability of the source data structure.

I suggest you are using the wrong tool to implement your requirement. TSQL is simply not intended and does not really support ETL. If you intend to go down this path, you will need some advanced skills.