BCP Insert through View vs Manual insert

2 votes

1 answer

147 views

                          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?


                        

Asked by kevinnwhat (2224 rep)

Apr 8, 2022, 06:32 PM
Last activity: Apr 9, 2022, 10:08 AM

BCP Insert through View vs Manual insert

Related Questions