从动态SQL中插入数据到表中，而不知道列名。

24 浏览
0 Comments

从动态SQL中插入数据到表中，而不知道列名。

我有一个返回未知列数的动态SQL：\n\"enter\n动态SQL的代码如下：\n

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)

\n和代码：\n

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);

\n

    \n

  1. 前四行始终相同：\nCityTo, CityFrom, TotalCarDealerPrice, TotalCarDealerCount

    2. \n

  2. 接下来的列不同：\n 
    TransLeader Price, TransLeader Count, AutoTrader Price, AutoTrader  Count, AO 
   Price, AO Count, CarSale Price, CarSale Count ...

    3. \n

  3. 动态SQL最大的列数为59：

    4. \n

\n是否可以将数据插入到以下表中：\n

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
)

\n如果我将动态查询的数据插入到@TR表中，而动态查询的列数不是63，而是30，那么我将看到以下错误：\n

\n列名或提供的值的数量与表定义不匹配。\n

\n如何编写有效的插入语句，而不知道结果动态SQL中将有多少列？

0