BCP Insert through View vs Manual insert

All we need is an easy explanation of the problem, so here it is.

Consider the following code

use tempdb
go

drop table if exists ImportTest1;
create table ImportTest1 (
 Column1    int null,
 Column2 int null,
 NewColumn int null
);
insert into ImportTest1 select 1,2,3 union select 4,5,6;
select * from ImportTest1;

drop table if exists DestinationTest1;
create table DestinationTest1 (
Column1 int null,
Column2 int null
);
select * from DestinationTest1;
GO

CREATE OR ALTER VIEW TestView1 AS 
SELECT Column1 AS Column1, Column2 AS Column2, NULL AS NewColumn FROM DestinationTest1
GO

If we run this

INSERT INTO TestView1 (Column1, Column2, NewColumn)
SELECT Column1, Column2, NewColumn FROM ImportTest1

It fails with error

Update or insert of view or function 'TestView1' failed because it contains a derived or constant field.

However, if I do the same thing through BCP, it works fine

BCP "SELECT Column1, Column2, NewColumn FROM tempdb..ImportTest1" QUERYOUT C:\BCPTest\test.txt -T -c -t,

BCP tempdb..TestView1 IN C:\BCPTest\test.txt -T -c -t,

What is happening here that allows BCP to import successfully through the view but we cannot run that manually?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

bcp is an ODBC API application.

It does application-style things, as opposed to being a simple T-SQL command:

In your example, bcp runs:

exec sp_describe_first_result_set N'select * from tempdb..TestView1'

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select * from tempdb..TestView1',1
select @p1

insert bulk tempdb..TestView1([Column1] int,[Column2] int)

exec sp_unprepare 1

So essentially, it is working out which columns are valid to insert to before running the bulk insert (using API syntax).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply