Setup Default From another Table

 By default, SQL server won't allows you to set default value from another table, and I got few queries on how to perform this. You can do this with the help of Triggers.  Although I won't suggest the use of triggers except for auditing purpose, there are few situations where we can't eliminate their usage.

 

CREATE TABLE LookUpTable (id INT IDENTITY PRIMARY KEY , Value VARCHAR(100) )
INSERT INTO LookUpTable SELECT 'One' UNION ALL SELECT 'Two' UNION ALL SELECT 'Three' UNION ALL SELECT 'Four'
GO
CREATE TABLE ChildTable (id INT, Value VARCHAR(100)) 
GO

ALTER TRIGGER t_i_ModifyDefault 
ON ChildTable
FOR INSERT

AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Value IS NULL)
    BEGIN
        UPDATE c
        SET Value = l.Value
        FROM ChildTable c 
                INNER JOIN LookUpTable l ON l.id = c.id
         INNER JOIN inserted i ON i.id = c.id WHERE i.Value IS null
    END
    
END

GO

INSERT INTO ChildTable(id) SELECT 1
INSERT INTO ChildTable SELECT 8, 'two'
SELECT * FROM ChildTable