How to set default value for column from another table in SQL Server 2005

Is it possible in SQL Server 2005 to set a default value for a column which comes fro开发者_开发知识库m another table through a query or something?

Yes.

You can use a scalar UDF in a default constraint.

This will be RBAR ("Row By Agonizing Row") for multi row inserts however and a trigger might well be more efficient.

I agree with both of Martin's points. Therefore, I use a trigger to avoid the potential performance penalty of RBAR in case someone does a mass import of data into the table. In the following code, we want MyTable.MyColumn set to be DefaultSource.DefaultMyColumn if no value was supplied for MyColumn and we have a populated default value. This assumes that there is a MyTable.DefaultId (preferably with a Foreign Key) so we can get the default value from the correct row in DefaultSource.

CREATE TRIGGER [dbo].[TR_MyTable_AI_Default] 
   ON  [dbo].[MyTable] 
   AFTER INSERT
AS 
SET NOCOUNT ON;

UPDATE m
   SET [MyColumn] = ds.[DefaultMyColumn]
  FROM inserted i
       JOIN [dbo].[MyTable] m ON i.[PrimaryKey] = m.[PrimaryKey]
       JOIN [dbo].[DefaultSource] ds ON i.[DefaultId] = ds.[DefaultId]
 WHERE a.[MyColumn] IS NULL
   AND df.[DefaultMyColumn] IS NOT NULL;

Do not try to update the inserted special table directly, but instead update the base table for the trigger.