SQL 插入语句 - 除某些列外的所有列的 FILL 空值-解网

问：

我有一个插入语句，我只需要填写 70 列中的 15 列 - 所有其他列都可以保持 NULL。我想找到一种方法来在更短的时间内写出这个语句，而不必把所有的空值都写成等等等等

INSERT INTO my_table
SELECT
division_id
,sub_division_id AS sub_division_id
,store
,store_id AS store_id
,store_address
,zip_code 
,'202306' AS week
,'2023-02-02' AS date
,'Type' AS type
,'other_string'  AS blah
,'Name' AS Name
,NULL AS unimportant_column
,NULL AS unimportant_column_2
,NULL AS unimportant_column_3
,NULL AS unimportant_column_4
,NULL AS unimportant_column_5
,NULL AS unimportant_column_6
,NULL AS unimportant_column_7
,NULL AS unimportant_column_8
,household_id
,NULL AS unimportant_column_9
,NULL 
,NULL
,id
,email AS emailaddress
,token
,NULL AS reg
,NULL AS blah
,NULL AS type
,NULL AS del 
,NULL AS las
,NULL AS las
,NULL AS fir
,NULL AS las
,NULL AS pro
,NULL AS pro_2
,NULL AS pro_3
,NULL AS prm
,NULL AS pro_5
,NULL AS pro_6
,NULL AS pro_7
,NULL AS pro_8
,NULL AS j4
,NULL AS j4_2
,NULL AS off_1
,NULL AS off02
,NULL AS off03
,NULL AS off04
,NULL AS off05
,NULL AS off_06
,NULL AS off07
,NULL AS of08
,NULL AS off09
,NULL AS off10
,NULL AS off11
,NULL AS off12
,NULL AS off13
,NULL AS of14
,NULL AS off15
,NULL AS off16
,NULL AS off17
,NULL AS off18
,NULL AS off19
,NULL AS off20
,NULL AS off21
,NULL AS off22
,NULL AS off23
,NULL AS off24
,NULL AS dade
,NULL as edyd

FROM some_table x

where flag1 = 1

and flag2 = 7



GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42
,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69 ;

我希望它是这样的

INSERT INTO my_table
SELECT
division_id
,sub_division_id AS sub_division_id
,store AS Store
,store_id AS store_id
,store_address as store_nm_addr
,zip_code as zip_code
,'202306' AS week
,'2023-02-02' AS date
,'TYPE' AS TYPE
,'other_string'  AS blah
,'Name' AS name
,household_id as household_id
,id as ID
,email AS emailaddress
,token_txt as token
FROM some_table x

where flag1 = 1

and flag2 = 7


;

当然，这不起作用，因为您收到此错误： SQL 编译错误：插入值列表与列列表不匹配，期望 70 但得到 15

但是，有什么方法可以只插入您需要的值并将其余值默认为 null 值呢？“my_table”表必须有 70 列 - 即使其他列中的值为 null

SQL 优化 snowflake-cloud-data-platform 简化可读性

INSERT INTO my_table (division_id, sub_division_id,
    Store, store_id, store_nm_addr, zip_code, week, date, TYPE, blah, name,
    household_id, ID, emailaddress, token)
SELECT
    division_id,
    sub_division_id,
    store,
    store_id,
    store_address,
    zip_code,
    '202306',
    '2023-02-02',
    'TYPE',
    'other_string',
    'Name',
    household_id,
    id,
    email,
    token_txt
FROM some_table x
WHERE flag1 = 1 AND flag2 = 7;

上一个：C# 不同参数类型的相同方法

下一个：为什么更漂亮会在这里多加一对括号？

SQL 插入语句 - 除某些列外的所有列的 FILL 空值

SQL insert statement - FILL null values for all columns except some

评论