Tablo sütununu atlamak için biçim dosyası kullanma (SQL Server)

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Bu makalede, atlanan sütunun verileri kaynak veri dosyasında olmadığında tablo sütununu içeri aktarmayı atlamak için biçim dosyasının nasıl kullanılacağı açıklanmaktadır. Veri dosyası, hedef tablodaki sütun sayısından daha az alan içerebilir; diğer bir ifadeyle, yalnızca hedef tabloda aşağıdaki iki koşuldan en az biri doğruysa sütunu içeri aktarmayı atlayabilirsiniz:

• Atlanan sütun null atanabilir.
• Atlanan sütunun varsayılan değeri vardır.

Uyarı

Toplu ekleme de dahil olmak üzere bu söz dizimi Azure Synapse Analytics'te desteklenmez. Azure Synapse Analytics ve diğer bulut veritabanı platformu tümleştirmelerinde, Azure Data Factory'deki COPY deyimi aracılığıyla veya COPY INTO ve PolyBase gibi T-SQL deyimlerini kullanarak veri taşımayı gerçekleştirin.

Örnek tablo ve veri dosyası

Bu makaledeki örneklerde şema altında adlı myTestSkipCol bir tablo beklenmiştir dbo . Bu tabloyu veya AdventureWorks gibi WideWorldImporters bir örnek veritabanında veya başka bir veritabanında oluşturabilirsiniz. Bu tabloyu aşağıdaki gibi oluşturun:

USE WideWorldImporters;
GO

CREATE TABLE myTestSkipCol
(
    Col1 SMALLINT,
    Col2 NVARCHAR (50) NULL,
    Col3 NVARCHAR (50) NOT NULL
);
GO

Bu makaledeki örneklerde örnek veri dosyası myTestSkipCol2.datda kullanılır: . Bu veri dosyası yalnızca iki alan içerir, ancak hedef tabloda üç sütun bulunur.

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Temel adımlar

Tablo sütununu atlamak için XML olmayan bir biçim dosyası veya XML biçim dosyası kullanabilirsiniz. Her iki durumda da iki adım vardır:

1. Varsayılan biçim dosyasını oluşturmak için bcp komut satırı yardımcı programını kullanın.
2. Metin düzenleyicisinde varsayılan biçim dosyasını değiştirin.

Değiştirilen biçim dosyasının, var olan her alanı hedef tablodaki ilgili sütunuyla eşlemesi gerekir. Ayrıca atlanacak tablo sütununu veya sütunları da belirtmelidir.

Örneğin, verileri tablosundan myTestSkipCol2.datmyTestSkipCol toplu olarak içeri aktarmak için biçim dosyasının ilk veri alanını Col1ile eşlemesi, atlayıp Col2ikinci alanı Col3ile eşlemesi gerekir.

Seçenek #1 - XML olmayan bir biçim dosyası kullanma

1. Adım - Varsayılan XML olmayan biçim dosyası oluşturma

Komut isteminde aşağıdaki bcp komutunu çalıştırarak örnek tablo için myTestSkipCol varsayılan XML olmayan biçimli bir dosya oluşturun:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Önemli

Bağımsız değişkeniyle -S bağlandığınız sunucu örneğinin adını belirtmeniz gerekebilir. Ayrıca ve bağımsız değişkenleriyle kullanıcı adını ve parolayı -U-P belirtmeniz gerekebilir. Daha fazla bilgi için bkz. bcp Yardımcı Programı.

Önceki komut XML olmayan bir biçim dosyası oluşturur. myTestSkipCol_Default.fmt Bu biçim dosyası, bcp tarafından oluşturulan form olduğundan varsayılan biçim dosyası olarak adlandırılır. Varsayılan biçim dosyası, veri dosyası alanları ve tablo sütunları arasındaki bire bir yazışmayı açıklar.

Aşağıdaki ekran görüntüsünde bu örnek varsayılan biçim dosyasındaki değerler gösterilmektedir.

Uyarı

Biçim dosyası alanları hakkında daha fazla bilgi için bkz. XML olmayan biçim dosyaları (SQL Server).

2. Adım - XML olmayan bir biçim dosyasını değiştirme

Varsayılan XML olmayan biçim dosyasını değiştirmek için iki alternatif vardır. Alternatiflerden biri, veri alanının veri dosyasında bulunmadığını ve karşılık gelen tablo sütununa veri eklenmeyecek olduğunu gösterir.

Tablo sütununu atlamak için, varsayılan XML olmayan biçim dosyasını düzenleyin ve aşağıdaki alternatif yöntemlerden birini kullanarak dosyayı değiştirin:

Seçenek #1 - Satırı kaldırma

Sütunu atlamak için tercih edilen yöntem aşağıdaki üç adımı içerir:

1. İlk olarak, kaynak veri dosyasında eksik olan bir alanı tanımlayan herhangi bir biçim dosyası satırını silin.
2. Ardından, silinen bir satırı izleyen her biçim dosyası satırının "Konak dosya alanı sırası" değerini azaltın. Hedef, veri dosyasındaki her veri alanının gerçek konumunu yansıtan 1 ile n arasında sıralı "Konak dosya alanı sırası" değerleridir.
3. Son olarak, "Sütun sayısı" alanındaki değeri, veri dosyasındaki gerçek alan sayısını yansıtacak şekilde azaltın.

Aşağıdaki örnek, tablonun varsayılan biçim dosyasını myTestSkipCol temel alır. Bu değiştirilmiş biçim dosyası, ilk veri alanını Col1ile eşler, atlar ve ikinci veri alanını Col3ile eşlerCol2. satırı Col2 silindi. İlk alandan sonraki sınırlayıcı da olarak \t,değiştirildi.

14.0
2
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Seçenek 2 - Satır tanımını değiştirme

Alternatif olarak, tablo sütununu atlamak için tablo sütununa karşılık gelen biçim dosyası satırının tanımını değiştirebilirsiniz. Bu biçim dosyası satırında, "ön ek uzunluğu", "konak dosyası veri uzunluğu" ve "sunucu sütun sırası" değerleri 0 olarak ayarlanmalıdır. Ayrıca, "sonlandırıcı" ve "sütun harmanlama" alanları "" (boş veya NULL değer olarak) olarak ayarlanmalıdır. "Sunucu sütun adı" değeri boş olmayan bir dize gerektirir, ancak gerçek sütun adı gerekli değildir. Kalan biçim alanları varsayılan değerlerini gerektirir.

Aşağıdaki örnek, tablonun varsayılan biçim dosyasından myTestSkipCol da türetilmiştir.

14.0
3
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       0       ""       0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

XML olmayan bir biçim dosyası içeren örnekler

Aşağıdaki örnekler, bu makalenin myTestSkipCol önceki bölümlerinde açıklanan örnek tabloyu ve myTestSkipCol2.dat örnek veri dosyasını temel alır.

BULK INSERT kullanma

Bu örnek, önceki bölümde açıklandığı gibi oluşturulan değiştirilmiş XML dışı biçimli dosyalardan birini kullanarak çalışır. Bu örnekte, değiştirilen biçim dosyası olarak adlandırılır myTestSkipCol2.fmt. Veri dosyasını toplu içeri aktarmak myTestSkipCol2.dat için kullanmak BULK INSERT için SQL Server Management Studio'da (SSMS) aşağıdaki kodu çalıştırın. Bilgisayarınızdaki örnek dosyaların konumu için dosya sistemi yollarını güncelleştirin.

USE WideWorldImporters;
GO

BULK INSERT myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO

SELECT *
FROM myTestSkipCol;
GO

Seçenek 2 - XML biçim dosyası kullanma

1. Adım - Varsayılan XML biçim dosyası oluşturma

Komut isteminde myTestSkipCol aşağıdaki bcp komutunu çalıştırarak örnek tablo için varsayılan XML biçim dosyasını oluşturun:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Önemli

Bağımsız değişkeniyle -S bağlandığınız sunucu örneğinin adını belirtmeniz gerekebilir. Ayrıca ve bağımsız değişkenleriyle kullanıcı adını ve parolayı -U-P belirtmeniz gerekebilir. Daha fazla bilgi için bkz. bcp Yardımcı Programı.

Önceki komut bir XML biçim dosyası oluşturur. myTestSkipCol_Default.xml Bu biçim dosyası, bcp tarafından oluşturulan form olduğundan varsayılan biçim dosyası olarak adlandırılır. Varsayılan biçim dosyası, veri dosyası alanları ve tablo sütunları arasındaki bire bir yazışmayı açıklar.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7" />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT" />
    <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR" />
    <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR" />
  </ROW>
</BCPFORMAT>

Uyarı

XML biçim dosyalarının yapısı hakkında bilgi için bkz. XML biçim dosyaları (SQL Server).

Adım 2 - XML biçim dosyasını değiştirme

Burada, myTestSkipCol2.xmlöğesini atlayan değiştirilmiş XML biçim dosyası yer alır Col2. FIELD ve ROWCol2 girişleri kaldırıldı ve girdiler yeniden numaralandırıldı. İlk alandan sonraki sınırlayıcı da olarak \t,değiştirildi.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7" />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT" />
    <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR" />
  </ROW>
</BCPFORMAT>

XML biçim dosyası içeren örnekler

Aşağıdaki örnekler, bu makalenin myTestSkipCol önceki bölümlerinde açıklanan örnek tabloyu ve myTestSkipCol2.dat örnek veri dosyasını temel alır.

Verileri tablosundan myTestSkipCol2.datmyTestSkipCol tabloya aktarmak için, örneklerde değiştirilmiş XML biçim dosyası myTestSkipCol2.xmlkullanılır.

TOPLU INSERT'i görünümle kullanma

XML biçim dosyasıyla, bcp komutunu veya BULK INSERT deyimini kullanarak doğrudan tabloya aktardığınızda sütunu atlayamazsınız. Ancak, tablonun son sütunu dışında tüm sütunlarına aktarabilirsiniz. Son sütun dışında herhangi bir sütunu atlamanız gerekiyorsa, hedef tablonun yalnızca veri dosyasında yer alan sütunları içeren bir görünümünü oluşturmanız gerekir. Ardından, bu dosyadaki verileri toplu olarak görünüme aktarabilirsiniz.

Aşağıdaki örnek, tabloda görünümü myTestSkipCol oluştururv_myTestSkipCol. Bu görünüm ikinci tablo sütununu atlar Col2. Örnek daha sonra veri dosyasını bu görünüme myTestSkipCol2.dat aktarmak için kullanırBULK INSERT.

SSMS'de aşağıdaki kodu çalıştırın. Bilgisayarınızdaki örnek dosyaların konumu için dosya sistemi yollarını güncelleştirin.

USE WideWorldImporters;
GO

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,
           Col3
    FROM myTestSkipCol;
GO

BULK INSERT v_myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.xml');
GO

OPENROWSET(BULK...) kullanma

kullanarak bir tablo sütununu OPENROWSET(BULK...)atlamak üzere XML biçim dosyası kullanmak için, seçme listesinde ve hedef tabloda sütunların açık bir listesini aşağıdaki gibi sağlamanız gerekir:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

Aşağıdaki örnek toplu satır kümesi sağlayıcısını OPENROWSET ve myTestSkipCol2.xml biçim dosyasını kullanır. Örnek, veri dosyasını tabloya myTestSkipCol2.datmyTestSkipCol toplu olarak içeri aktarır. deyimi, gerektiğinde seçme listesindeki ve hedef tablodaki sütunların açık bir listesini içerir.

SSMS'de aşağıdaki kodu çalıştırın. Bilgisayarınızdaki örnek dosyaların konumu için dosya sistemi yollarını güncelleştirin.

USE WideWorldImporters;
GO

INSERT INTO myTestSkipCol (Col1, Col3)
SELECT Col1,
       Col3
FROM OPENROWSET (
    BULK 'C:\myTestSkipCol2.Dat',
    FORMATFILE = 'C:\myTestSkipCol2.Xml'
) AS t1;
GO

İlgili içerik

• bcp Yardımcı Programı
• TOPLU INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Bir veri alanını atlamak için biçim dosyası kullanma (SQL Server)
• Tablo sütunlarını veri dosyası alanlarına (SQL Server) eşlemek için biçim dosyası kullanma
• Verileri toplu içeri aktarmak için biçim dosyası kullanma (SQL Server)