Sample Header Ad - 728x90

How to use bcp in on a table with an indexed view in SQL Server

4 votes
1 answer
791 views
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
3. Delete the test data from Table1:
DELETE FROM [dbo].[Table1]
4. 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.* 4. 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]' 5. 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')
Asked by 1 . (143 rep)
Apr 5, 2022, 06:34 PM
Last activity: Apr 7, 2022, 10:09 PM