Post reply

Can I have a default table value based on another column???

  • December 8, 2016 at 10:22 am

    #319613

    Hi

    I'm using a table to store old records I upload from a file

    Can I have a default value of a table column based on RIGHT side of another column value

    Something like RIGHT(MyColumn, LEN(MyColumn) - 2) ??

    for example column named POID = 39123456789 and want to always drop the first two characters and store 123456789 in column named OID

    Thanks

  • December 8, 2016 at 10:34 am

    #1916860

    yes, but you need a case statement to bulletproof the calculation for data shorter than your assumption.

    CREATE TABLE EXAMPLE (

    ExampleId int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    MyColumn varchar(30)

    )

    INSERT INTO EXAMPLE(MyColumn)

    VALUES( ''),('1234567890')

    ALTER TABLE EXAMPLE ADD MyCalculation AS

    CASE

    WHEN LEN(MyColumn) >=2

    THEN RIGHT(MyColumn, LEN(MyColumn) - 2)

    ELSE MyColumn

    END PERSISTED

    SELECT * FROM EXAMPLE

    --DROP TABLE EXAMPLE

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 8, 2016 at 10:39 am

    #1916863

    Thanks for the quick response and sorry for the lack of explanation on my part!!

    I am using a Bulk insert via a txt file and was hoping to use the default value option on the column properties of the column I would like it in (OID)

  • December 8, 2016 at 10:45 am

    #1916865

    I'm confused. Do you want to have a default or a computed column?

    For a default, you might need a trigger to handle that option.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 8, 2016 at 11:05 am

    #1916872

    jbalbo (12/8/2016)

    Thanks for the quick response and sorry for the lack of explanation on my part!!

    I am using a Bulk insert via a txt file and was hoping to use the default value option on the column properties of the column I would like it in (OID)

    As Luis Identified, I mixed up default with calculated column in my rush to answer.

    a bulk insert will disable triggers by default,so a trigger might not work unless you add the command to enable the trigger for the bulk insert.

    why not just update your other column with the case statement immediately after the bulk insert?(update Table TABLE OtherColumn = CASE ... WHERE OtherColumn IS NULL)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 8, 2016 at 11:56 am

    #1916882

    Great Idea, never even thought of that will get back with results...

    Thank you!!

Viewing 6 posts - 1 through 6 (of 6 total)

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