Insert statement with column list - where non-existent columns are changed to Null value on insert

Has anyone seen an easy way to do this? I want to insert values from one table to another, but the source table has 8 columns and the destination table has 30.  The destination table is not permanent. The column names stay the same but it's basically a table for staging.  I want to be able to quickly insert those 8 (the column names are the same) and change the non-existent column name to insert NULL or '' (either works for this purpose).  I was looking for some type of scripting that finds and replaces those columns names.  Like a find non-existent names of columns, replace with null kind of thing.  Am I dreaming?  Thanks in advance!    p.s. i know I can go through and type Null on all of them, that's ok, I'll do that while waiting for the answer, but just thought I'd see if someone already knows this. I looked on Stack Overflow but it's hard to search for this without bringing up irrelevant posts.  Not even sure if this could be part of SQL Prompt, grasping at straws. Thanks again!!! 
Tagged:

Answers

  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @KristineAlmond!

    There isn't a feature that will do that exactly, but have you tried using the autocomplete option for the destination table? You can have SQL Prompt create an insert statement with default values, similar to below:



    You could then use "Script as Insert" on the rows in the source table, and then manually combine the two as needed.

    (The manual work is not ideal I know, but might help!)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.