Can I give a column in a view a default value for inserts?

Some third party software uses a "staging" table for various imports, so that anything we try to import can be validated through their business logic. Any table in the system can be a target, and it is standard practice to assign col1 as the "type" of import you are performing.

All the columns in the table are generic, ie. "data1", "data2", "data3" etc...

So col1 is the designated "type" column and would be set to "ContactImport" or "ProductImport". I'd like to not actually return the "type" column... or at least rename it to "ImportType."

So I've started creating views to better manage our imports/exports. However it seems like it could be error prone if people are using the v_ContactImport view and have to manually set col1 t开发者_JS百科o "ContactImport" right?

So is there a way I can have the view give a default value to col1.

Since I'm rushing and not sure how clear that is, here's a quick example of the view itself:

CREATE VIEW v_ContactImport 
AS

SELECT data2 as FirstName,
       data3 as LastName,
       data4 as Phone       
FROM StagingTable
WHERE data1='ContactImport'

And would like to just do

INSERT INTO v_ContactImport (FirstName, LastName, Phone)
VALUES ('Jack', 'Handey', '5555555555' )

So then if I select v_ContactImport that record would come back because data1 would automatically be set to 'ContactImport'

Kinda guessing this isn't possible, but thought I'd ask.

You could define your view, and then (in SQL Server 2005 and up, and there's probably similar structures in other DBMSs) create an INSTEAD OF trigger. Cutting, pasting, and modifying from BOL, it would look something like:

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on v_ContactImport
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement that adds in your column
  INSERT INTO StagingTable
       SELECT 'ContactImport', Col1, Col2, ColEtc
       FROM inserted
END
GO

You could use an instead of trigger:

See: http://msdn.microsoft.com/en-us/library/def01zh2(VS.90).aspx