Bulk insert to View

Question

Bulk insert to View

michanne

SSCrazy

Points: 2329
More actions
January 30, 2004 at 11:16 am

#160714

Hello all,
Why would you Bulk insert to a view instead of a table and what are the advantages/dissadvantages?

Thank you

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

The view must be updatable. In order to make the view updatable, The view has to be created with INSTEAD OF Triggers and trigger will slow the performance in general. You also can created updated partitioned view subject to certain restrictions.

From BOL.

"If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:

The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:
```
CREATE VIEW NoTable ASSELECT GETDATE() AS CurrentDate,       @@LANGUAGE AS CurrentLanguage,       CURRENT_USER AS CurrentUser
```

INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable, as specified in the conditions above. UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view. A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause."

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

I usually bcp (or bulk insert) into a view when the file has fewer columns than my original table and I do not want to create a work table to receive the data or I do want to make transfornations

Bye
Gabor