Post reply

Drop Column and Update column in sql

  • February 16, 2015 at 10:10 pm

    #297839

    Hi I am looking for standard sql code for below 2 concern

    1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

    2-I want to update all the table where table name start with ArchiveBbx

    example:-

    Update table Archivebbxfbcc

    set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

    Please help me to achive my scenario

    thanks in advance 🙂

    Regards,

    Vipin jha

  • February 16, 2015 at 10:54 pm

    #1777943

    vipin_jha123 (2/16/2015)

    Hi I am looking for standard sql code for below 2 concern

    1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

    2-I want to update all the table where table name start with ArchiveBbx

    example:-

    Update table Archivebbxfbcc

    set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

    Please help me to achive my scenario

    thanks in advance 🙂

    Regards,

    Vipin jha

    I'm confused. First, why would you in step 1 want to drop the RowCheckSum column from all tables that follow a pattern and then think you can update that now dropped column in step 2? Or are you saying that "drop to all tables" is your way of saying that you want to add the column to the tables? If the later, you really need to stop using the word "drop" for such a thing because "drop" usually means "to remove". It's not like dropping a letter to someone.

    My other question is why on this good green Earth are you using MD5? It's one of the slowest hashes there is and suffers from much more non-uniqueness than most any of the others.

    Other than that, you've written most of what you need. You just need to read the table names from one of the system views and then a little dynamic SQL to loop through them. Have a look at sys.objects and INFORMATION_SCHEMA.tables in Books Online for more information.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2015 at 11:18 pm

    #1777945

    Hi ,

    I want to have chcecksum value of all the column in my table. In ssis pacakge I used script component MD5 , but now I have converted ssis to SQL Script, where I dint found any other approach.

    in SSIS i used to get my checksum value like 6KHlSG7t4eTaduLySN0/FQ==

    but in SQL I am getting checksum value like 0xFDA0F387AA72F5256D870014532C4E0C by using

    HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003) AS varchar(max))) command.

    if you can help me to get checksum value like 6KHlSG7t4eTaduLySN0/FQ== in sql , then I dont have to drop the column and update the same.

    or is there any way to get cheksum value of all the column.

    please help me

  • February 16, 2015 at 11:31 pm

    #1777948

    vipin_jha123 (2/16/2015)

    Hi ,

    I want to have chcecksum value of all the column in my table. In ssis pacakge I used script component MD5 , but now I have converted ssis to SQL Script, where I dint found any other approach.

    in SSIS i used to get my checksum value like 6KHlSG7t4eTaduLySN0/FQ==

    but in SQL I am getting checksum value like 0xFDA0F387AA72F5256D870014532C4E0C by using

    HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003) AS varchar(max))) command.

    if you can help me to get checksum value like 6KHlSG7t4eTaduLySN0/FQ== in sql , then I dont have to drop the column and update the same.

    or is there any way to get cheksum value of all the column.

    please help me

    The 0xnnnnnnn is a checksum and is likely the hexadecimal representation of what you were seeing in SSIS. Also, if you lookup HASHBYTES in Books Online (the help system that SQL Server uses), you'll see many more options other than MD5.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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