我需要从另一个表中部分填充一个临时表，其余的填充一个静态值。

Question

我使用的是TSql，我已经创建了一个包含23列的临时表。我需要用静态值填充来自另一个表的前15列和最后8列。原因是我在最后一个查询中使用Union将临时表与查询数据结合在一起，而后者有23列。

我使用惰性into语句来填充前15列，没有问题，但我不知道如何添加静态列。

create table #Inspections (CMPR_PKG_SQ int, 
    CREA_TMS datetime, 
    [SPEC_SQ] int, 
    [LOT_ID_TXT] varchar(50), 
    [SMPL_NUM] varchar(15), 
    [WPI_NUM] char(6), 
    [WPI_SEG_NUM] char(1), 
    [WP_PHAS_GRP_CD] char(1), 
    [WP_PHAS_TYP_CD] char(1), 
    [FINPROJ_SQ] char(2), 
    [MIX_DSGN_SQ] int, 
    [FCLTY_SQ] int, 
    District char(2), 
    [Mix #] char(280), 
    [Mix Type] char(128), 
    [3/8" Percent Passing] decimal(15,3), 
    [4 Percent Passing] decimal(15,3), 
    [8 Percent Passing] decimal(15,3), 
    [200 Percent Passing] decimal(15,3), 
    [Percent Content] decimal(15,3), 
    [Percent Voids] decimal(15,3), 
    [Density] decimal(15,3), 
    CF decimal(15,3))

insert into #Inspections (CMPR_PKG_SQ,
     CREA_TMS,
     [SPEC_SQ],
     [LOT_ID_TXT],
     [SMPL_NUM],
     [WPI_NUM],
     [WPI_SEG_NUM],
     [WP_PHAS_GRP_CD],
     [WP_PHAS_TYP_CD],
     [FINPROJ_SQ],
     [MIX_DSGN_SQ],
     [FCLTY_SQ],
     District,
     [Mix #],
     [Mix Type])
Select CMPR_PKG_SQ,
     CREA_TMS,
     [SPEC_SQ],
     [LOT_ID_TXT],
     [SMPL_NUM],
     [WPI_NUM],
     [WPI_SEG_NUM],
     [WP_PHAS_GRP_CD],
     [WP_PHAS_TYP_CD],
     [FINPROJ_SQ],
     [MIX_DSGN_SQ],
     [FCLTY_SQ],
     District,
     [Mix #],
     [Mix Type]
from SourceTable

如何将N/A添加到其余的列中？

Answer 1

可以在select语句中提供文字/常量。

insert into #Inspections (CMPR_PKG_SQ,
     CREA_TMS,
     [SPEC_SQ],
     [LOT_ID_TXT],
     [SMPL_NUM],
     [WPI_NUM],
     [WPI_SEG_NUM],
     [WP_PHAS_GRP_CD],
     [WP_PHAS_TYP_CD],
     [FINPROJ_SQ],
     [MIX_DSGN_SQ],
     [FCLTY_SQ],
     District,
     [Mix #],
     [Mix Type],

     -- Additional columns...
     [3/8" Percent Passing])
Select CMPR_PKG_SQ,
     CREA_TMS,
     [SPEC_SQ],
     [LOT_ID_TXT],
     [SMPL_NUM],
     [WPI_NUM],
     [WPI_SEG_NUM],
     [WP_PHAS_GRP_CD],
     [WP_PHAS_TYP_CD],
     [FINPROJ_SQ],
     [MIX_DSGN_SQ],
     [FCLTY_SQ],
     District,
     [Mix #],
     [Mix Type],

     -- hard coded values
     19.783 AS [3/8" Percent Passing]
from SourceTable

Answer 2

我想你应该在栏下加上“NA”。

[3/8" Percent Passing],
[4 Percent Passing],
[8 Percent Passing],
[200 Percent Passing],
[Percent Content],
[Percent Voids],
[Density] &
CF decimal]

如果这是正确的，查询将不会插入，因为它需要一个十进制(15,3)，并且您提供它的字符串，即‘'NA’

从理论上讲，如果属性类型是Varchars，这应该可以工作。

insert into #Inspections (CMPR_PKG_SQ,
     CREA_TMS,
     [SPEC_SQ],
     [LOT_ID_TXT],
     [SMPL_NUM],
     [WPI_NUM],
     [WPI_SEG_NUM],
     [WP_PHAS_GRP_CD],
     [WP_PHAS_TYP_CD],
     [FINPROJ_SQ],
     [MIX_DSGN_SQ],
     [FCLTY_SQ],
     District,
     [Mix #],
     [Mix Type],
    'N/A'  as   [3/8" Percent Passing],
    'N/A'  as   [4 Percent Passing],
    'N/A'  as   [8 Percent Passing],
    'N/A'  as   [200 Percent Passing],
    'N/A'  as   [Percent Content],
    'N/A'  as   [Percent Voids],
    'N/A'  as   [Density],
    'N/A'  as   [CF decimal])
from SourceTable