4

I'm using SQL Server 2017 and want to use "bcp in" in a script to populate tables in several databases. I am unable to import data into a table that has an indexed view. The following is an MCVE that reproduces my problem:

  1. Run the script at the end of this post to populate a test database with two tables, an indexed view and some data.

  2. Run bcp out to export the test data from the table Table1 to a file:

bcp [dbo].[Table1] out .\Table1.bcp -S "localhost" -d TestDB -T -k -N
  1. Delete the test data from Table1:
DELETE FROM [dbo].[Table1]
  1. Attempt to import data into Table1 using bcp in:
bcp [dbo].[Table1] in .\Table1.bcp -S "localhost" -d TestDB -T -k -N

Result: fails with an error message INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...

Note: if I drop the index [ix_v1] on the view, this will succeed: the problem only occurs if the table is referenced by an indexed view.

  1. Attempt to import data into Table1 using bcp in with the -q switch:
bcp [dbo].[Table1] in .\Table1.bcp -S "localhost" -d TestDB -T -k -N -q

Result: fails with an error message Invalid object name '[dbo].[Table1]'

  1. Attempt to import data into Table1 by specifying the table name without [] delimiters, and with the -q switch:
bcp dbo.Table1 in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q

Result: the data is successfully imported. However this does not meet my requirements, because I want a generic script that will also work with table names that require delimiters (e.g. [dbo].[My Table]).

Question: Is there a way to use bcp to import data into a table with an indexed view, while specifying a delimited, schema-qualified table name on the bcp command line?

Script to populate an empty database TestDB

USE [TestDB]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
    [Table1Id] [int] NOT NULL,
    [Table1Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Table1Id] ASC)
 )
GO
CREATE TABLE [dbo].[Table2](
    [Table2Id] [int] NOT NULL,
    [Table2Name] [nvarchar](50) NOT NULL,
    [Table1Id] [int] NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED (  [Table2Id] ASC)
)
GO
CREATE VIEW [dbo].[v1] WITH SCHEMABINDING
AS
    SELECT 
    T1.Table1Id, T1.Table1Name,
    T2.Table2Id, T2.Table2Name
    FROM [dbo].[Table1] T1 INNER JOIN [dbo].[Table2] T2
    ON T1.Table1Id = T2.Table1Id
GO
CREATE UNIQUE CLUSTERED INDEX [ix_v1] ON [dbo].[v1] (Table1Name, Table2Name)
GO
INSERT INTO Table1
VALUES 
 (1, 'One')
,(2,'Two')

1 Answer 1

6

The documentation for the bcp -q option states:

Enclose the entire three-part table or view name in quotation marks ("")

Instead of enclosing the individual object name parts in square brackets, enclose the entire qualified object name argument in double-quotes. 2-part names are allowed here since you specified the -d option for the database context:

bcp "dbo.Table1" in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q

This will allow object names that don't conform to regular identifier naming rules. The -q option will additionally SET QUOTED_IDENTIFIER ON to allow inserting into a tables with indexed views, filtered indexes, etc.

However, you'll still get an error if the table name contains a period. An undocumented work-around is to enclose both the schema and object name in double-quotes:

bcp "dbo"."Table.NameWithDot" in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q
6
  • That works, thanks. Seems a bit bizarre that quoted identifiers should be required in this specific case - would you agree that this appears to be a bug in the bcp.exe utility?
    – Joe
    Apr 6 at 6:47
  • @Joe, this isn't a bug. The example I provided worked but is contrary to the documentation. I corrected my answer. Please review.
    – Dan Guzman
    Apr 6 at 11:08
  • The recommendation to enclose the entire qualified object name in quotes doesn't work in the (admittedly pathological) case of schema and/or table names that contain a period character. So I've gone for your original solution of quoting schema name and table name separately. Whether it's a bug or a feature is subjective, but when something apparently unrelated like adding an indexed view breaks existing import scripts, I'd lean towards calling it a bug.
    – Joe
    Apr 6 at 14:02
  • @joe, I didn't try the period character. One could make the argument it's a design shortcoming rather than a bug but the end result is the same.
    – Dan Guzman
    Apr 6 at 14:55
  • 1
    @Joe, I added the undocumented method to my answer.
    – Dan Guzman
    Apr 7 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.