Insert data into table from dynamic SQL without knowing column names

Question

I have a dynamic SQL which returns unknowing count of columns:

The code of dynamic SQL is:

DECLARE @RawTable TABLE 
(
  ID INT NOT NULL
, CarDealer VARCHAR(100)
, id_CarDealer INT
, CityTo VARCHAR(100) NULL
, CityFrom VARCHAR(100) NULL
, Price NUMERIC(18,3) NULL
, CarCount INT
)


INSERT INTO @RawTable
(
ID,
CarDealer,
id_CarDealer,
CityTo,
CityFrom,
Price,
CarCount
)
VALUES
  (   1, 'TransLeader', 1, 'New York', 'Los Angeles', 5000, 10)
, (   2, 'AutoTrader',  2, 'New York', 'Los Angeles', 6000, 11)
, (   3, 'AO',          3, 'New York', 'Paris',       7000, 20)
, (   4,  'CarSale',    4, 'Paris',    'London',      4000, 50)

and code:

Declare @SQL varchar(max) = Stuff((Select Distinct ','+QuoteName(CarDealer+' Price') 
                                                  +','+QuoteName(CarDealer+' Count') 
                                    From RawTable 
                                    Order By 1 
                                    For XML Path('')
                                   ),1,1,'') 

Select  @SQL = '
Select CityTo
      ,CityFrom
      ,[TotalCarDealerPrice]
      ,[TotalCarDealerCount]
      ,'+@SQL+'
From (
        Select A.CityTo
              ,A.CityFrom
              ,B.*
         From  RawTable A
         Cross Apply ( values (''TotalCarDealerPrice'',Price)
                             ,(''TotalCarDealerCount'',CarCount)
                             ,(CarDealer+'' Price''   ,Price)
                             ,(CarDealer+'' Count''   ,CarCount)
                     ) B (Item,Value)
     ) A
 Pivot (sum([Value]) For [Item] in ([TotalCarDealerPrice],[TotalCarDealerCount],' + @SQL + ') ) p'
--Print @SQL
Exec(@SQL);

The first four rows will be always the same:

CityTo, CityFrom, TotalCarDealerPrice, TotalCarDealerCount
Then the next columns will not be the same:

TransLeader Price, TransLeader Count, AutoTrader Price, AutoTrader Count, AO Price, AO Count, CarSale Price, CarSale Count ...
The max quantity of columns of a dynamic SQL will be 59:

Is it possible to insert data in the following table:

DECLARE @TR TABLE 
(
CityTo VARCHAR(255), CityFrom VARCHAR(255), TotalCarDealerPrice VARCHAR(255),         
TotalCarDealerCount VARCHAR(255),

Dealer1  DECIMAL(18,3) NULL, DealerPrice1 INT, Dealer2  DECIMAL(18,3) NULL,     
DealerPrice2 INT, Dealer3  DECIMAL(18,3) NULL, DealerPrice3 INT, Dealer4  
DECIMAL(18,3) NULL, DealerPrice4 INT,  Dealer5   DECIMAL(18,3) NULL, 
DealerPrice5 INT,
Dealer6  DECIMAL(18,3) NULL, DealerPrice6 INT, Dealer7  DECIMAL(18,3) NULL, 
DealerPrice7 INT, Dealer8  DECIMAL(18,3) NULL, DealerPrice8 INT, Dealer9  
DECIMAL(18,3) NULL, DealerPrice9 INT,  Dealer10  DECIMAL(18,3) NULL, 
DealerPrice10 INT,
Dealer11 DECIMAL(18,3) NULL, DealerPrice11 INT, Dealer12  DECIMAL(18,3) NULL, 
DealerPrice12 INT, Dealer13  DECIMAL(18,3) NULL, DealerPrice13 INT, Dealer14  
DECIMAL(18,3) NULL, DealerPrice14 INT,  Dealer15  DECIMAL(18,3) NULL, 
DealerPrice15 INT,
Dealer16 DECIMAL(18,3) NULL, DealerPrice16 INT, Dealer17  DECIMAL(18,3) NULL, 
DealerPrice17 INT, Dealer18  DECIMAL(18,3) NULL, DealerPrice18 INT, Dealer19  
DECIMAL(18,3) NULL, DealerPrice19 INT,  Dealer20  DECIMAL(18,3) NULL, 
DealerPrice20 INT,
Dealer21 DECIMAL(18,3) NULL, DealerPrice21 INT, Dealer22  DECIMAL(18,3) NULL, 
DealerPrice22 INT, Dealer23  DECIMAL(18,3) NULL, DealerPrice23 INT, Dealer24  
DECIMAL(18,3) NULL, DealerPrice24 INT,  Dealer25  DECIMAL(18,3) NULL, 
DealerPrice25 INT,
Dealer26 DECIMAL(18,3) NULL, DealerPrice26 INT, Dealer27  DECIMAL(18,3) NULL, 
DealerPrice27 INT, Dealer28  DECIMAL(18,3) NULL, DealerPrice28 INT, Dealer29  
DECIMAL(18,3) NULL, DealerPrice29 INT,  Dealer30  DECIMAL(18,3) NULL, 
DealerPrice30 INT,
Dealer31 DECIMAL(18,3) NULL, DealerPrice31 INT, Dealer32  DECIMAL(18,3) NULL, 
DealerPrice32 INT, Dealer33  DECIMAL(18,3) NULL, DealerPrice33 INT, Dealer34  
DECIMAL(18,3) NULL, DealerPrice34 INT,  Dealer35  DECIMAL(18,3) NULL, 
DealerPrice35 INT,
Dealer36 DECIMAL(18,3) NULL, DealerPrice36 INT, Dealer37  DECIMAL(18,3) NULL, 
DealerPrice37 INT, Dealer38  DECIMAL(18,3) NULL, DealerPrice38 INT, Dealer39  
DECIMAL(18,3) NULL, DealerPrice39 INT,  Dealer40  DECIMAL(18,3) NULL, 
DealerPrice40 INT,
Dealer41 DECIMAL(18,3) NULL, DealerPrice41 INT, Dealer42  DECIMAL(18,3) NULL, 
DealerPrice42 INT, Dealer43  DECIMAL(18,3) NULL, DealerPrice43 INT, Dealer44  
DECIMAL(18,3) NULL, DealerPrice44 INT,  Dealer45  DECIMAL(18,3) NULL, 
DealerPrice45 INT,
Dealer46 DECIMAL(18,3) NULL, DealerPrice46 INT, Dealer47  DECIMAL(18,3) NULL, 
DealerPrice47 INT, Dealer48  DECIMAL(18,3) NULL, DealerPrice48 INT, Dealer49  
DECIMAL(18,3) NULL, DealerPrice49 INT,  Dealer50  DECIMAL(18,3) NULL, 
DealerPrice50 INT,
Dealer51 DECIMAL(18,3) NULL, DealerPrice51 INT, Dealer52  DECIMAL(18,3) NULL, 
DealerPrice52 INT, Dealer53  DECIMAL(18,3) NULL, DealerPrice53 INT, Dealer54  
DECIMAL(18,3) NULL, DealerPrice54 INT,  Dealer55  DECIMAL(18,3) NULL, 
DealerPrice55 INT
)

If I insert data from dynamic query into @TR table and count of columns of dynamic query is not 63, but 30, then I see the error:

Column name or number of supplied values does not match table definition.

How to write valid insert statement without knowledge of how many columns will be in result dynamic SQL?

Of course it is possible to insert into that table. Just like any other table with an insert statement. But what is your actual question here? — Sean Lange, Sep 18 '18 at 14:47
Sounds like you are trying to do this [SELECT INTO a table variable in T-SQL](https://stackoverflow.com/questions/3838240/select-into-a-table-variable-in-t-sql) — Jacob H, Sep 18 '18 at 14:51
@SeanLange if I insert data from dynamic query into @TR table and count of columns of dynamic query is not 63, but 30, then I see error `Column name or number of supplied values does not match table definition.`. How to insert data? — StepUp, Sep 18 '18 at 14:57
By providing a valid insert statement. I don't mean to sound snarky here but this is so vague. Just like any insert to a table. If you don't specify the columns then you must provide a value for each column. — Sean Lange, Sep 18 '18 at 15:00
@SeanLange ok. How to write valid insert statement without knowledge of how many columns will be in result dynamic SQL? — StepUp, Sep 18 '18 at 15:07
You can't. You have to know how many columns are in a table to write an insert statement. But I don't even see where you are creating this table. The challenge here is that you are very in tune with your requirement but you are NOT sharing that information. You see what you really need here is a question, once you provide the question so that others understand the answer will manifest itself. — Sean Lange, Sep 18 '18 at 15:23
Isn't that dynamic pivot supposed to be the very final operation to prepare data for **displaying**? Why don't you do what you need to do with these data **before** pivot? — Ivan Starostin, Sep 18 '18 at 16:24
@IvanStarostin cause there is a requirement to insert pivot data in another table with predefined column names. — StepUp, Sep 18 '18 at 16:28
Build insert statement with dynamic sql too, place NULLs for missing columns (since full column list is **predefined**, you can always know which columns are missing in pivot output), put data into # (because of dynamic sql), move **predefined full column list** from # into @. — Ivan Starostin, Sep 18 '18 at 16:34
@IvanStarostin My SQL skills is very poor so I've asked question here. I do not how to do it. It would be very helpful if you show how to do it. Thanks in advance. — StepUp, Sep 18 '18 at 16:39

Insert data into table from dynamic SQL without knowing column names

0 Answers0

Linked