Post reply

How to Insert Column in and existing table

  • May 28, 2021 at 11:43 am

    #3889168

    Hello Community,

    I have a table which has the following fields, EntityID, EntityColumnOrder,ColumnName, ColumnType.

    You will notice that the Columns in the ColumnName field are listed vertically.

    InsertColumns

    I have the attached csv which, as you can see has the columns going across horizontally.

    Can someone show me how to insert the columns in the spreadsheet into the table vertically, also I would like to insert a EntityID number for the columns as 144, and a ColumnType of StringType()

    I know my description is very sparse, but I hope what I'm asking makes sense.

     

    Attachments:
    You must be logged in to view attached files.

  • May 28, 2021 at 12:08 pm

    #3889175

    The columns in the spreadsheet do not appear to correspond to the columns in your screenshot, making this a bit of a puzzle.

    Why not just hit 'TRANSPOSE' in the spreadsheet, if you want to switch rows and columns?

    Can you clarify the requirement?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 28, 2021 at 12:38 pm

    #3889190

    Hi Phil,

    Thanks for reaching out.

    I know my question is a little confusing.

    Basically, I would want the columns inserted as follows:

    InsertColumns

  • May 28, 2021 at 1:11 pm

    #3889217

    OK, that helps.

    So you are looking for a routine which imports only the first row of a spreadsheet/CSV file, transposes that row of data into separate rows and adds them to your target table? Presumably with a brand new EntityId? If not, how is the EntityId generated? Shouldn't CD_CREATED_DATE have EntityId 144 in your screenshot?

    • This reply was modified 3 years, 5 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 28, 2021 at 1:13 pm

    #3889219

    Also, how is the value of ColumnType determined?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 28, 2021 at 1:31 pm

    #3889223

    Hi Phil

    Does this make sense?

  • May 28, 2021 at 1:48 pm

    #3889227

    Phil,

    Yes, that is exactly what I want.... I couldn't have explained it better myself..

    Can you please, please help me with that?

  • May 28, 2021 at 1:49 pm

    #3889228

    With regard to the EntityID, I will create manually...

  • May 28, 2021 at 1:57 pm

    #3889232

    Hi Phil,

    The following sample code my help...

    CREATE TABLE #tmpTable (
        EntityColumnID int,
        EntityID int,
        EntityColumnOrder int,
        ColumnName varchar(250),
        ColumnType varchar(250),
        ColumnRename varchar(250),
        IsPrimaryKey bit,
        IsChangeTracking bit,
        IsNullable bit,
        SliceType varchar(50),
        Active bit,
        ColumnDescription varchar(500),
        IsMetadataColumn bit)

    INSERT #tmpTable VALUES
    (3048,143,14,'geo_enabled','BooleanType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the geo_enabled id column',CONVERT(bit, 'False')),
    (3049,143,15,'lang','StringType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the lang column',CONVERT(bit, 'False')),
    (3050,143,16,'attributes_list','ArrayType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the attributes_list id column',CONVERT(bit, 'False')),
    (3051,143,17,'change_key_hash','StringType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the change_key_hash column',CONVERT(bit, 'False')),
    (3052,143,18,'primary_key_hash','StringType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the primary_key_hash column',CONVERT(bit, 'False')),
    (3053,143,19,'load_month','StringType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the load_month column',CONVERT(bit, 'False')),
    (3054,143,20,'load_day','StringType()','',CONVERT(bit, 'False'),CONVERT(bit, 'False'),CONVERT(bit, 'True'),'',CONVERT(bit, 'True'),'the load_day column',CONVERT(bit, 'False'))

    SELECT * FROM #tmpTable

    The above is a sample table with the columns I originally mentioned plus some additional columns

    If this doesn't help, then don't worry - what you said is what I want.

     

  • May 28, 2021 at 4:03 pm

    #3889294

    OK, understood. Unfortunately, I do not have time today to help any further. If no one else answers in the meantime, I will try to get back to you in the coming days.

    PS, what about ColumnType? Where does the value come from?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 28, 2021 at 7:11 pm

    #3889360

    Hi Phil,

    Thanks for support with this. Hopefully, someone will help in your absence.

    ColumnType is manually entered

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply