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

I have an insert statement where I only need to fill 15 columns out of 70 - and all the other columns can remain NULL. I would like to find a way to write this statement in a shorter matter without having to write all the null as etc etc

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 ;

I want it to be something like this

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


;

Ofcourse this does not work as you get this error: SQL compilation error: Insert value list does not match column list expecting 70 but got 15

But what would be a way to insert just the values you need and default the rest to null values? The "my_table" table must have 70 columns - even though the values in the other columns are null

CodePudding user response：

The problem with your current insert is that if you do not specify any column names then it defaults to expecting all columns. If you explicitly list out the target columns you want, then the omitted names will default to receiving a null value.

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;