INSERT (Transact-SQL)
Berlaku untuk: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics Analytics
Platform System (PDW)
Warehouse di Microsoft Fabric
Menambahkan satu atau beberapa baris ke tabel atau tampilan di SQL Server. Misalnya, lihat Contoh.
Sintaksis
-- Syntax for SQL Server and Azure SQL Database
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table
| execute_statement
| <dml_table_source>
| DEFAULT VALUES
}
}
}
[;]
<object> ::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name
}
<dml_table_source> ::=
SELECT <select_list>
FROM ( <dml_statement_with_output_clause> )
[AS] table_alias [ ( column_alias [ ,...n ] ) ]
[ WHERE <search_condition> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
-- External tool only syntax
INSERT
{
[BULK]
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
( <column_definition> )
[ WITH (
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] KEEP_NULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] TABLOCK ]
) ]
}
[; ] <column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
INSERT [INTO] { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ( column_name [ ,...n ] ) ]
{
VALUES ( { NULL | expression } )
| SELECT <select_criteria>
}
[ OPTION ( <query_option> [ ,...n ] ) ]
[;]
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
WITH <common_table_expression>
Menentukan kumpulan hasil bernama sementara, juga dikenal sebagai ekspresi tabel umum, yang ditentukan dalam cakupan pernyataan INSERT. Kumpulan hasil berasal dari pernyataan SELECT. Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL).
TOP (ekspresi) [ PERCENT ]
Menentukan jumlah atau persentase baris acak yang akan disisipkan. ekspresi dapat berupa angka atau persentase baris. Untuk informasi selengkapnya, lihat TOP (Transact-SQL).
INTO
Adalah kata kunci opsional yang dapat digunakan antara INSERT dan tabel target.
server_name
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Adalah nama server tertaut tempat tabel atau tampilan berada. server_name dapat ditentukan sebagai nama server tertaut, atau dengan menggunakan fungsi OPENDATASOURCE.
Ketika server_name ditentukan sebagai server tertaut, database_name dan schema_name diperlukan. Ketika server_name ditentukan dengan OPENDATASOURCE, database_name dan schema_name mungkin tidak berlaku untuk semua sumber data dan tunduk pada kemampuan penyedia OLE DB yang mengakses objek jarak jauh.
database_name
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Adalah nama database.
schema_name
Adalah nama skema tempat tabel atau tampilan berada.
table_or view_name
Adalah nama tabel atau tampilan yang akan menerima data.
Variabel tabel, dalam cakupannya, dapat digunakan sebagai sumber tabel dalam pernyataan INSERT.
Tampilan yang dirujuk oleh table_or_view_name harus dapat diperbarui dan mereferensikan tepat satu tabel dasar dalam klausa FROM tampilan. Misalnya, INSERT ke dalam tampilan multi-tabel harus menggunakan column_list yang hanya mereferensikan kolom dari satu tabel dasar. Untuk informasi selengkapnya tentang tampilan yang dapat diperbarui, lihat CREATE VIEW (Transact-SQL).
rowset_function_limited
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Apakah fungsi OPENQUERY atau OPENROWSET. Penggunaan fungsi-fungsi ini tunduk pada kemampuan penyedia OLE DB yang mengakses objek jarak jauh.
DENGAN ( <table_hint_limited> [ ... n ] )
Menentukan satu atau beberapa petunjuk tabel yang diizinkan untuk tabel target. Kata kunci WITH dan tanda kurung diperlukan.
READPAST, NOLOCK, dan READUNCOMMITTED tidak diizinkan. Untuk informasi selengkapnya tentang petunjuk tabel, lihat Petunjuk Tabel (Transact-SQL).
Penting
Kemampuan untuk menentukan petunjuk HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, atau UPDLOCK pada tabel yang merupakan target pernyataan INSERT akan dihapus dalam versi SQL Server yang akan datang. Petunjuk ini tidak memengaruhi performa pernyataan INSERT. Hindari menggunakannya dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakannya.
Menentukan petunjuk TABLOCK pada tabel yang merupakan target pernyataan INSERT memiliki efek yang sama seperti menentukan petunjuk TABLOCKX. Kunci eksklusif diambil di atas meja.
(column_list)
Adalah daftar satu atau beberapa kolom untuk menyisipkan data. column_list harus diapit tanda kurung dan dibatasi oleh koma.
Jika kolom tidak dalam column_list, Mesin Database harus dapat memberikan nilai berdasarkan definisi kolom; jika tidak, baris tidak dapat dimuat. Mesin Database secara otomatis menyediakan nilai untuk kolom jika kolom:
Memiliki properti IDENTITY. Nilai identitas inkremental berikutnya digunakan.
Memiliki default. Nilai default untuk kolom digunakan.
Memiliki jenis data tanda waktu. Nilai tanda waktu saat ini digunakan.
Dapat diubah ke null. Nilai null digunakan.
Adalah kolom komputasi. Nilai terhitung digunakan.
column_list harus digunakan saat nilai eksplisit dimasukkan ke dalam kolom identitas, dan opsi SET IDENTITY_INSERT harus AKTIF untuk tabel.
Klausul OUTPUT
Mengembalikan baris yang disisipkan sebagai bagian dari operasi sisipkan. Hasilnya dapat dikembalikan ke aplikasi pemrosesan atau dimasukkan ke dalam tabel atau variabel tabel untuk pemrosesan lebih lanjut.
Klausa OUTPUT tidak didukung dalam pernyataan DML yang mereferensikan tampilan partisi lokal, tampilan terdistribusi, atau tabel jarak jauh, atau pernyataan INSERT yang berisi execute_statement. Klausa OUTPUT INTO tidak didukung dalam pernyataan INSERT yang berisi <klausa dml_table_source> . Untuk informasi selengkapnya tentang argumen dan perilaku klausa ini, lihat Klausul OUTPUT (Transact-SQL).
NILAI
Memperkenalkan daftar atau daftar nilai data yang akan disisipkan. Harus ada satu nilai data untuk setiap kolom di column_list, jika ditentukan, atau dalam tabel. Daftar nilai harus diapit dalam tanda kurung.
Jika nilai dalam daftar Nilai tidak dalam urutan yang sama dengan kolom dalam tabel atau tidak memiliki nilai untuk setiap kolom dalam tabel, column_list harus digunakan untuk secara eksplisit menentukan kolom yang menyimpan setiap nilai masuk.
Anda dapat menggunakan konstruktor baris Transact-SQL (juga disebut konstruktor nilai tabel) untuk menentukan beberapa baris dalam satu pernyataan INSERT. Konstruktor baris terdiri dari satu klausa VALUES dengan beberapa daftar nilai yang diapit dalam tanda kurung dan dipisahkan oleh koma. Untuk informasi selengkapnya, lihat Konstruktor Nilai Tabel (Transact-SQL).
Catatan
Konstruktor nilai tabel tidak didukung di Azure Synapse Analytics. Sebagai gantinya, pernyataan berikutnya INSERT
dapat dijalankan untuk menyisipkan beberapa baris. Di Azure Synapse Analytics, nilai sisipkan hanya dapat berupa nilai literal konstan atau referensi variabel. Untuk menyisipkan non-literal, atur variabel ke nilai non-konstanta dan sisipkan variabel.
DEFAULT
Memaksa Mesin Database memuat nilai default yang ditentukan untuk kolom. Jika default tidak ada untuk kolom dan kolom mengizinkan nilai null, NULL akan disisipkan. Untuk kolom yang ditentukan dengan jenis data tanda waktu, nilai tanda waktu berikutnya disisipkan. DEFAULT tidak valid untuk kolom identitas.
expression
Adalah konstanta, variabel, atau ekspresi. Ekspresi tidak boleh berisi pernyataan EXECUTE.
Saat mereferensikan jenis data karakter Unicode nchar, nvarchar, dan ntext, 'ekspresi' harus diawali dengan huruf kapital 'N'. Jika 'N' tidak ditentukan, SQL Server mengonversi string ke halaman kode yang sesuai dengan kolase default database atau kolom. Karakter apa pun yang tidak ditemukan di halaman kode ini hilang.
derived_table
Adalah pernyataan SELECT valid yang mengembalikan baris data yang akan dimuat ke dalam tabel. Pernyataan SELECT tidak boleh berisi ekspresi tabel umum (CTE).
execute_statement
Adalah pernyataan EXECUTE valid yang mengembalikan data dengan pernyataan SELECT atau READTEXT. Untuk informasi selengkapnya, lihat EXECUTE (Transact-SQL).
Opsi RESULT SETS dari pernyataan EXECUTE tidak dapat ditentukan dalam INSERT... Pernyataan EXEC.
Jika execute_statement digunakan dengan INSERT, setiap kumpulan hasil harus kompatibel dengan kolom dalam tabel atau di column_list.
execute_statement dapat digunakan untuk menjalankan prosedur tersimpan di server yang sama atau server jarak jauh. Prosedur di server jarak jauh dijalankan, dan kumpulan hasil dikembalikan ke server lokal dan dimuat ke dalam tabel di server lokal. Dalam transaksi terdistribusi, execute_statement tidak dapat dikeluarkan terhadap server tertaut loopback ketika koneksi mengaktifkan beberapa kumpulan hasil aktif (MARS).
Jika execute_statement mengembalikan data dengan pernyataan READTEXT, setiap pernyataan READTEXT dapat mengembalikan maksimum 1 MB (1024 KB) data. execute_statement juga dapat digunakan dengan prosedur yang diperluas. execute_statement menyisipkan data yang dikembalikan oleh utas utama prosedur yang diperluas; namun, output dari utas selain utas utama tidak dimasukkan.
Anda tidak dapat menentukan parameter bernilai tabel sebagai target pernyataan INSERT EXEC; namun, ini dapat ditentukan sebagai sumber dalam string INSERT EXEC atau prosedur tersimpan. Untuk informasi selengkapnya, lihat Menggunakan Parameter Bernilai Tabel (Mesin Database).
<dml_table_source>
Menentukan bahwa baris yang disisipkan ke dalam tabel target adalah baris yang dikembalikan oleh klausa OUTPUT pernyataan INSERT, UPDATE, DELETE, atau MERGE, yang difilter secara opsional oleh klausa WHERE. Jika <dml_table_source> ditentukan, target pernyataan INSERT luar harus memenuhi batasan berikut:
Ini harus tabel dasar, bukan tampilan.
Ini tidak bisa menjadi tabel jarak jauh.
Ini tidak dapat memiliki pemicu yang ditentukan di atasnya.
Ini tidak dapat berpartisipasi dalam hubungan kunci utama-asing.
Ini tidak dapat berpartisipasi dalam replikasi penggabungan atau langganan yang dapat diperbarui untuk replikasi transaksional.
Tingkat kompatibilitas database harus diatur ke 100 atau lebih tinggi. Untuk informasi selengkapnya, lihat Klausul OUTPUT (Transact-SQL).
<select_list>
Adalah daftar yang dipisahkan koma yang menentukan kolom mana yang dikembalikan oleh klausa OUTPUT yang akan disisipkan. Kolom di <select_list> harus kompatibel dengan kolom tempat nilai disisipkan. <> select_list tidak dapat mereferensikan fungsi agregat atau TEXTPTR.
Catatan
Variabel apa pun yang tercantum dalam daftar SELECT mengacu pada nilai aslinya, terlepas dari perubahan apa pun yang dilakukan pada variabel tersebut dalam <dml_statement_with_output_clause>.
<dml_statement_with_output_clause>
Adalah pernyataan INSERT, UPDATE, DELETE, atau MERGE yang valid yang mengembalikan baris yang terpengaruh dalam klausa OUTPUT. Pernyataan tidak boleh berisi klausa WITH, dan tidak dapat menargetkan tabel jarak jauh atau tampilan yang dipartisi. Jika UPDATE atau DELETE ditentukan, itu tidak bisa berupa UPDATE atau DELETE berbasis kursor. Baris sumber tidak dapat direferensikan sebagai pernyataan DML berlapis.
WHERE <search_condition>
Adalah klausa WHERE apa pun yang berisi search_condition> valid <yang memfilter baris yang dikembalikan oleh <dml_statement_with_output_clause>. Untuk informasi selengkapnya, lihat Kondisi Pencarian (Transact-SQL). Ketika digunakan dalam konteks ini, <search_condition> tidak dapat berisi subkueri, fungsi skalar yang ditentukan pengguna yang melakukan akses data, fungsi agregat, TEXTPTR, atau predikat pencarian teks lengkap.
NILAI DEFAULT
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Memaksa baris baru berisi nilai default yang ditentukan untuk setiap kolom.
MASSAL
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Digunakan oleh alat eksternal untuk mengunggah aliran data biner. Opsi ini tidak ditujukan untuk digunakan dengan alat seperti SQL Server Management Studio, SQLCMD, OSQL, atau antarmuka pemrograman aplikasi akses data seperti SQL Server Native Client.
FIRE_TRIGGERS
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Menentukan bahwa setiap pemicu sisipan yang ditentukan pada tabel tujuan dijalankan selama operasi pengunggahan aliran data biner. Untuk informasi selengkapnya, lihat SISIPAN MASSAL (Transact-SQL).
CHECK_CONSTRAINTS
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Menentukan bahwa semua batasan pada tabel target atau tampilan harus diperiksa selama operasi pengunggahan aliran data biner. Untuk informasi selengkapnya, lihat SISIPAN MASSAL (Transact-SQL).
KEEPNULLS
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Menentukan bahwa kolom kosong harus mempertahankan nilai null selama operasi pengunggahan aliran data biner. Untuk informasi selengkapnya, lihat Menyimpan Null atau Menggunakan Nilai Default Selama Impor Massal (SQL Server).
KILOBYTES_PER_BATCH = kilobytes_per_batch
Menentukan perkiraan jumlah kilobyte (KB) data per batch sebagai kilobytes_per_batch. Untuk informasi selengkapnya, lihat SISIPAN MASSAL (Transact-SQL).
ROWS_PER_BATCH =rows_per_batch
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
Menunjukkan perkiraan jumlah baris data dalam aliran data biner. Untuk informasi selengkapnya, lihat SISIPAN MASSAL (Transact-SQL).
Catatan
Kesalahan sintaks dimunculkan jika daftar kolom tidak disediakan.
Keterangan
Untuk informasi khusus untuk menyisipkan data ke dalam tabel grafik SQL, lihat INSERT (SQL Graph).
Praktik Terbaik
Gunakan fungsi @@ROWCOUNT untuk mengembalikan jumlah baris yang disisipkan ke aplikasi klien. Untuk informasi selengkapnya, lihat @@ROWCOUNT (Transact-SQL).
Praktik Terbaik untuk Mengimpor Data Secara Massal
Menggunakan INSERT INTO... SELECT untuk Mengimpor data secara massal dengan pengelogan dan paralelisme minimal
Anda dapat menggunakan INSERT INTO <target_table> SELECT <columns> FROM <source_table>
untuk mentransfer sejumlah besar baris secara efisien dari satu tabel, seperti tabel penahapan, ke tabel lain dengan pengelogan minimal. Pengelogan minimal dapat meningkatkan performa pernyataan dan mengurangi kemungkinan operasi mengisi ruang log transaksi yang tersedia selama transaksi.
Pengelogan minimal untuk pernyataan ini memiliki persyaratan berikut:
- Model pemulihan database diatur ke sederhana atau dicatat secara massal.
- Tabel target adalah timbunan kosong atau tidak kosong.
- Tabel target tidak digunakan dalam replikasi.
TABLOCK
Petunjuk ditentukan untuk tabel target.
Baris yang disisipkan ke dalam tumpukan sebagai hasil dari tindakan sisipkan dalam pernyataan MERGE juga mungkin dicatat secara minimal.
BULK INSERT
Tidak seperti pernyataan, yang memegang kunci Pembaruan Massal (BU) yang kurang ketat, INSERT INTO … SELECT
dengan TABLOCK
petunjuk memegang kunci eksklusif (X) pada tabel. Ini berarti Anda tidak dapat menyisipkan baris menggunakan beberapa operasi penyisipan yang dijalankan secara bersamaan.
Namun, dimulai dengan SQL Server 2016 (13.x) dan tingkat kompatibilitas database 130, satu INSERT INTO … SELECT
pernyataan dapat dijalankan secara paralel saat memasukkan ke tumpukan atau indeks penyimpan kolom berkluster (CCI). Sisipan paralel dimungkinkan TABLOCK
saat menggunakan petunjuk.
Paralelisme untuk pernyataan di atas memiliki persyaratan berikut, yang mirip dengan persyaratan untuk pengelogan minimal:
- Tabel target adalah timbunan kosong atau tidak kosong.
- Tabel target memiliki indeks penyimpan kolom berkluster (CCI) tetapi tidak ada indeks non-kluster.
- Tabel target tidak memiliki kolom identitas dengan IDENTITY_INSERT diatur ke NONAKTIF.
TABLOCK
Petunjuk ditentukan untuk tabel target.
Untuk skenario di mana persyaratan untuk pengelogan minimal dan penyisipan paralel terpenuhi, kedua peningkatan akan bekerja sama untuk memastikan throughput maksimum operasi pemuatan data Anda.
Untuk informasi selengkapnya tentang menggunakan INSERT di Gudang Anda di Microsoft Fabric, lihat Menyerap data ke gudang Anda menggunakan Transact-SQL.
Catatan
Penyisipan ke dalam tabel sementara lokal (diidentifikasi oleh awalan #) dan tabel sementara global (diidentifikasi oleh awalan ##) juga diaktifkan untuk paralelisme menggunakan petunjuk TABLOCK.
Menggunakan OPENROWSET dan BULK untuk Mengimpor data secara Massal
Fungsi OPENROWSET dapat menerima petunjuk tabel berikut, yang menyediakan pengoptimalan beban massal dengan pernyataan INSERT:
TABLOCK
Petunjuk dapat meminimalkan jumlah rekaman log untuk operasi penyisipan. Model pemulihan database harus diatur ke sederhana atau dicatat secara massal dan tabel target tidak dapat digunakan dalam replikasi. Untuk informasi selengkapnya, lihat Prasyarat untuk Pengelogan Minimal dalam Impor Massal.TABLOCK
Petunjuk dapat mengaktifkan operasi penyisipan paralel. Tabel target adalah tumpukan atau indeks penyimpan kolom berkluster (CCI) tanpa indeks non-kluster, dan tabel target tidak dapat memiliki kolom identitas yang ditentukan.IGNORE_CONSTRAINTS
Petunjuk untuk sementara dapat menonaktifkan pemeriksaan batasan FOREIGN KEY dan CHECK.IGNORE_TRIGGERS
Petunjuk dapat menonaktifkan eksekusi pemicu untuk sementara waktu.KEEPDEFAULTS
Petunjuk memungkinkan penyisipan nilai default kolom tabel, jika ada, alih-alih NULL ketika catatan data tidak memiliki nilai untuk kolom.KEEPIDENTITY
Petunjuk memungkinkan nilai identitas dalam file data yang diimpor digunakan untuk kolom identitas dalam tabel target.
Pengoptimalan ini mirip dengan yang tersedia dengan BULK INSERT
perintah . Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).
Jenis Data
Saat Anda menyisipkan baris, pertimbangkan perilaku jenis data berikut:
Jika nilai sedang dimuat ke dalam kolom dengan tipe data karakter, varchar, atau varbinary , padding atau pemotongan kosong berikutnya (spasi untuk karakter dan varchar, nol untuk varbinary) ditentukan oleh pengaturan SET ANSI_PADDING yang ditentukan untuk kolom saat tabel dibuat. Untuk informasi selengkapnya, lihat SET ANSI_PADDING (Transact-SQL).
Tabel berikut ini memperlihatkan operasi default untuk SET ANSI_PADDING NONAKTIF.
Jenis Data Operasi default Char Nilai Pad dengan spasi ke lebar kolom yang ditentukan. varchar Hapus spasi berikutnya ke karakter non-spasi terakhir atau ke karakter spasi tunggal untuk string yang hanya terdiri dari spasi. varbinary Hapus nol berikutnya. Jika string kosong (' ') dimuat ke dalam kolom dengan jenis data varchar atau teks , operasi defaultnya adalah memuat string dengan panjang nol.
Menyisipkan nilai null ke dalam kolom teks atau gambar tidak membuat penunjuk teks yang valid, juga tidak melakukan pra-alokasi halaman teks 8 KB.
Kolom yang dibuat dengan penyimpanan tipe data pengidentifikasi unik secara khusus memformat nilai biner 16 byte. Tidak seperti kolom identitas, Mesin Database tidak secara otomatis menghasilkan nilai untuk kolom dengan jenis data pengidentifikasi unik. Selama operasi penyisipan, variabel dengan jenis data uniqueidentifier dan konstanta string dalam bentuk xxxxxxxx-xxxx-xxxx-xxx (36 karakter termasuk tanda hubung, di mana x adalah digit heksadesimal dalam rentang 0-9 atau a-f) dapat digunakan untuk kolom pengidentifikasi unik. Misalnya, 6F9619FF-8B86-D011-B42D-00C04FC964FF adalah nilai yang valid untuk variabel atau kolom pengidentifikasi unik. Gunakan fungsi NEWID() untuk mendapatkan ID unik global (GUID).
Menyisipkan Nilai ke dalam Kolom Tipe yang Ditentukan Pengguna
Anda bisa menyisipkan nilai dalam kolom jenis yang ditentukan pengguna dengan:
Menyediakan nilai jenis yang ditentukan pengguna.
Menyediakan nilai dalam jenis data sistem SQL Server, selama jenis yang ditentukan pengguna mendukung konversi implisit atau eksplisit dari jenis tersebut. Contoh berikut menunjukkan cara menyisipkan nilai dalam kolom jenis
Point
yang ditentukan pengguna , dengan mengonversi secara eksplisit dari string.INSERT INTO Cities (Location) VALUES ( CONVERT(Point, '12.3:46.2') );
Nilai biner juga dapat disediakan tanpa melakukan konversi eksplisit, karena semua jenis yang ditentukan pengguna secara implisit dapat dikonversi dari biner.
Memanggil fungsi yang ditentukan pengguna yang mengembalikan nilai jenis yang ditentukan pengguna. Contoh berikut menggunakan fungsi
CreateNewPoint()
yang ditentukan pengguna untuk membuat nilai baru jenis yang ditentukanPoint
pengguna dan menyisipkan nilai keCities
dalam tabel.INSERT INTO Cities (Location) VALUES ( dbo.CreateNewPoint(x, y) );
Penanganan Kesalahan
Anda dapat menerapkan penanganan kesalahan untuk pernyataan INSERT dengan menentukan pernyataan dalam TRY... KONSTRUKSI CATCH.
Jika pernyataan INSERT melanggar batasan atau aturan, atau jika memiliki nilai yang tidak kompatibel dengan jenis data kolom, pernyataan gagal dan pesan kesalahan dikembalikan.
Jika INSERT memuat beberapa baris dengan SELECT atau EXECUTE, pelanggaran aturan atau batasan apa pun yang terjadi dari nilai yang dimuat menyebabkan pernyataan dihentikan, dan tidak ada baris yang dimuat.
Ketika pernyataan INSERT mengalami kesalahan aritmatika (luapan, bagi dengan nol, atau kesalahan domain) yang terjadi selama evaluasi ekspresi, Mesin Database menangani kesalahan ini seolah-olah SET ARITHABORT diatur ke AKTIF. Batch dihentikan, dan pesan kesalahan dikembalikan. Selama evaluasi ekspresi saat SET ARITHABORT dan SET ANSI_WARNINGS NONAKTIF, jika pernyataan INSERT, DELETE, atau UPDATE mengalami kesalahan aritmetika, luapan, membagi berdasarkan nol, atau kesalahan domain, SQL Server menyisipkan atau memperbarui nilai NULL. Jika kolom target tidak dapat diubah ke null, tindakan sisipkan atau perbarui gagal dan pengguna menerima kesalahan.
Interoperabilitas
Saat pemicu INSTEAD OF
didefinisikan pada tindakan INSERT terhadap tabel atau tampilan, pemicu dijalankan alih-alih pernyataan INSERT. Untuk informasi selengkapnya tentang INSTEAD OF
pemicu, lihat CREATE TRIGGER (Transact-SQL).
Batasan dan Pembatasan
Saat Anda menyisipkan nilai ke dalam tabel jarak jauh dan tidak semua nilai untuk semua kolom ditentukan, Anda harus mengidentifikasi kolom tempat nilai yang ditentukan akan disisipkan.
Ketika TOP digunakan dengan INSERT baris yang dirujuk tidak diatur dalam urutan apa pun dan klausa ORDER BY tidak dapat ditentukan secara langsung dalam pernyataan ini. Jika Anda perlu menggunakan TOP untuk menyisipkan baris dalam urutan kronologis yang bermakna, Anda harus menggunakan TOP bersama dengan klausa ORDER BY yang ditentukan dalam pernyataan subpilih. Lihat bagian Contoh yang mengikuti topik ini.
KUERI INSERT yang menggunakan SELECT dengan ORDER BY untuk mengisi baris menjamin bagaimana nilai identitas dihitung tetapi bukan urutan di mana baris disisipkan.
Di Gudang Data Paralel, klausul ORDER BY tidak valid dalam VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, fungsi sebaris, tabel turunan, subkueri, dan ekspresi tabel umum, kecuali TOP juga ditentukan.
Perilaku Pengelogan
Pernyataan INSERT selalu dicatat sepenuhnya kecuali saat menggunakan fungsi OPENROWSET dengan kata kunci MASSAL atau saat menggunakan INSERT INTO <target_table> SELECT <columns> FROM <source_table>
. Operasi ini dapat dicatat secara minimal. Untuk informasi selengkapnya, lihat bagian "Praktik Terbaik untuk Memuat Data Massal" sebelumnya dalam topik ini.
Keamanan
Selama koneksi server tertaut, server pengirim menyediakan nama login dan kata sandi untuk terhubung ke server penerima atas namanya. Agar koneksi ini berfungsi, Anda harus membuat pemetaan masuk antara server tertaut dengan menggunakan sp_addlinkedsrvlogin.
Ketika Anda menggunakan OPENROWSET(BULK...), penting untuk memahami bagaimana SQL Server menangani peniruan identitas. Untuk informasi selengkapnya, lihat "Pertimbangan Keamanan" dalam Mengimpor Data Massal dengan Menggunakan INSERT MASSAL atau OPENROWSET(BULK...) (SQL Server).
Izin
Izin INSERT diperlukan pada tabel target.
IZIN INSERT default untuk anggota sysadmin
peran server tetap, db_owner
peran database tetap, dan db_datawriter
pemilik tabel. Anggota sysadmin
, db_owner
, dan db_securityadmin
peran, dan pemilik tabel dapat mentransfer izin ke pengguna lain.
Untuk menjalankan INSERT dengan opsi OPENROWSET function BULK, Anda harus menjadi anggota sysadmin
peran server tetap atau peran bulkadmin
server tetap.
Contoh
Kategori | Elemen sintaksis unggulan |
---|---|
Sintaks dasar | INSERT * konstruktor nilai tabel |
Menangani nilai kolom | IDENTITY * NEWID * nilai default * jenis yang ditentukan pengguna |
Menyisipkan data dari tabel lain | MEMASUKKAN... PILIH * SISIPKAN... EXECUTE * WITH common table expression * TOP * OFFSET FETCH |
Menentukan objek target selain tabel standar | Tampilan * variabel tabel |
Menyisipkan baris ke dalam tabel jarak jauh | Server tertaut * Fungsi set baris OPENQUERY * Fungsi set baris OPENDATASOURCE |
Memuat data secara massal dari tabel atau file data | MEMASUKKAN... SELECT * Fungsi OPENROWSET |
Mengesampingkan perilaku default pengoptimal kueri dengan menggunakan petunjuk | Petunjuk tabel |
Menangkap hasil pernyataan INSERT | Klausa OUTPUT |
Sintaks dasar
Contoh di bagian ini menunjukkan fungsionalitas dasar pernyataan INSERT menggunakan sintaks minimum yang diperlukan.
J. Menyisipkan satu baris data
Contoh berikut menyisipkan satu baris ke Production.UnitMeasure
dalam tabel dalam database AdventureWorks2022. Kolom dalam tabel ini adalah UnitMeasureCode
, , Name
dan ModifiedDate
. Karena nilai untuk semua kolom disediakan dan tercantum dalam urutan yang sama dengan kolom dalam tabel, nama kolom tidak harus ditentukan dalam daftar kolom*.*
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
B. Menyisipkan beberapa baris data
Contoh berikut menggunakan konstruktor nilai tabel untuk menyisipkan tiga baris ke dalam Production.UnitMeasure
tabel dalam database AdventureWorks2022 dalam satu pernyataan INSERT. Karena nilai untuk semua kolom disediakan dan tercantum dalam urutan yang sama dengan kolom dalam tabel, nama kolom tidak harus ditentukan dalam daftar kolom.
Catatan
Konstruktor nilai tabel tidak didukung di Azure Synapse Analytics.
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923')
, (N'Y3', N'Cubic Yards', '20080923');
C. Menyisipkan data yang tidak dalam urutan yang sama dengan kolom tabel
Contoh berikut menggunakan daftar kolom untuk secara eksplisit menentukan nilai yang disisipkan ke dalam setiap kolom. Urutan kolom dalam Production.UnitMeasure
tabel dalam database AdventureWorks2022 adalah UnitMeasureCode
, , ModifiedDate
Name
; namun, kolom tidak tercantum dalam urutan tersebut dalam column_list.
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
Menangani Nilai Kolom
Contoh di bagian ini menunjukkan metode menyisipkan nilai ke dalam kolom yang ditentukan dengan properti IDENTITY, nilai DEFAULT, atau didefinisikan dengan jenis data seperti uniqueidentifier atau kolom jenis yang ditentukan pengguna.
D. Menyisipkan data ke dalam tabel dengan kolom yang memiliki nilai default
Contoh berikut menunjukkan menyisipkan baris ke dalam tabel dengan kolom yang secara otomatis menghasilkan nilai atau memiliki nilai default. Column_1
adalah kolom komputasi yang secara otomatis menghasilkan nilai dengan menggabungkan string dengan nilai yang disisipkan ke dalam column_2
. Column_2
didefinisikan dengan batasan default. Jika nilai tidak ditentukan untuk kolom ini, nilai default akan digunakan. Column_3
didefinisikan dengan jenis data rowversion , yang secara otomatis menghasilkan angka biner unik dan bertahas. Column_4
tidak secara otomatis menghasilkan nilai. Ketika nilai untuk kolom ini tidak ditentukan, NULL disisipkan. Pernyataan INSERT menyisipkan baris yang berisi nilai untuk beberapa kolom tetapi tidak semua. Dalam pernyataan INSERT terakhir, tidak ada kolom yang ditentukan dan hanya nilai default yang disisipkan dengan menggunakan klausa NILAI DEFAULT.
CREATE TABLE dbo.T1
(
column_1 AS 'Computed column ' + column_2,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('my column default'),
column_3 rowversion,
column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4)
VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4)
VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2)
VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES;
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO
E. Menyisipkan data ke dalam tabel dengan kolom identitas
Contoh berikut menunjukkan metode yang berbeda untuk menyisipkan data ke dalam kolom identitas. Dua pernyataan INSERT pertama memungkinkan nilai identitas dibuat untuk baris baru. Pernyataan INSERT ketiga mengambil alih properti IDENTITY untuk kolom dengan pernyataan SET IDENTITY_INSERT dan menyisipkan nilai eksplisit ke dalam kolom identitas.
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2)
VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO
F. Menyisipkan data ke dalam kolom pengidentifikasi unik dengan menggunakan NEWID()
Contoh berikut menggunakan fungsi NEWID() untuk mendapatkan GUID untuk column_2
. Tidak seperti untuk kolom identitas, Mesin Database tidak secara otomatis menghasilkan nilai untuk kolom dengan jenis data pengidentifikasi unik, seperti yang ditunjukkan oleh pernyataan kedua INSERT
.
CREATE TABLE dbo.T1
(
column_1 int IDENTITY,
column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2)
VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES;
GO
SELECT column_1, column_2
FROM dbo.T1;
G. Menyisipkan data ke dalam kolom jenis yang ditentukan pengguna
Pernyataan Transact-SQL berikut menyisipkan tiga baris ke PointValue
dalam kolom Points
tabel. Kolom ini menggunakan tipe yang ditentukan pengguna (UDT) CLR. Jenis Point
data terdiri dari nilai bilangan bulat X dan Y yang diekspos sebagai properti UDT. Anda harus menggunakan fungsi CAST atau CONVERT untuk mentransmisikan nilai X dan Y yang dibatasi koma ke jenis .Point
Dua pernyataan pertama menggunakan fungsi CONVERT untuk mengonversi nilai string ke Point
jenis , dan pernyataan ketiga menggunakan fungsi CAST. Untuk informasi selengkapnya, lihat Memanipulasi Data UDT.
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));
Menyisipkan Data dari Tabel Lain
Contoh di bagian ini menunjukkan metode menyisipkan baris dari satu tabel ke tabel lain.
H. Menggunakan opsi SELECT dan EXECUTE untuk menyisipkan data dari tabel lain
Contoh berikut menunjukkan cara menyisipkan data dari satu tabel ke tabel lain dengan menggunakan INSERT... SELECT atau INSERT... MENJALANKAN. Masing-masing didasarkan pada pernyataan SELECT multi-tabel yang menyertakan ekspresi dan nilai harfiah dalam daftar kolom.
Pernyataan INSERT pertama menggunakan pernyataan SELECT untuk mendapatkan data dari tabel sumber (Employee
, , dan Person
) dalam database AdventureWorks2022 dan menyimpan hasil yang ditetapkan dalam EmployeeSales
SalesPerson
tabel. Pernyataan INSERT kedua menggunakan klausul EXECUTE untuk memanggil prosedur tersimpan yang berisi pernyataan SELECT, dan INSERT ketiga menggunakan klausa EXECUTE untuk mereferensikan pernyataan SELECT sebagai string harfiah.
CREATE TABLE dbo.EmployeeSales
( DataSource varchar(20) NOT NULL,
BusinessEntityID varchar(11) NOT NULL,
LastName varchar(40) NOT NULL,
SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales
AS
SET NOCOUNT ON;
SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales
EXECUTE
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE ''2%''
ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
I. Menggunakan ekspresi tabel umum WITH untuk menentukan data yang disisipkan
Contoh berikut membuat NewEmployee
tabel dalam database AdventureWorks2022. Ekspresi tabel umum (EmployeeTemp
) menentukan baris dari satu atau beberapa tabel yang akan disisipkan ke NewEmployee
dalam tabel. Pernyataan INSERT mereferensikan kolom dalam ekspresi tabel umum.
CREATE TABLE HumanResources.NewEmployee
(
EmployeeID int NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL,
PhoneNumber Phone NULL,
AddressLine1 nvarchar(60) NOT NULL,
City nvarchar(30) NOT NULL,
State nchar(3) NOT NULL,
PostalCode nvarchar(15) NOT NULL,
CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,
Address, City, StateProvince,
PostalCode, CurrentFlag)
AS (SELECT
e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
a.AddressLine1, a.City, sp.StateProvinceCode,
a.PostalCode, e.CurrentFlag
FROM HumanResources.Employee e
INNER JOIN Person.BusinessEntityAddress AS bea
ON e.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
INNER JOIN Person.PersonPhone AS pp
ON e.BusinessEntityID = pp.BusinessEntityID
INNER JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Person.Person as c
ON e.BusinessEntityID = c.BusinessEntityID
)
INSERT INTO HumanResources.NewEmployee
SELECT EmpID, LastName, FirstName, Phone,
Address, City, StateProvince, PostalCode, CurrentFlag
FROM EmployeeTemp;
GO
j. Menggunakan TOP untuk membatasi data yang disisipkan dari tabel sumber
Contoh berikut membuat tabel EmployeeSales
dan menyisipkan nama dan data penjualan tahun ke tanggal untuk 5 karyawan acak teratas dari tabel HumanResources.Employee
dalam database AdventureWorks2022. Pernyataan INSERT memilih 5 baris yang dikembalikan oleh SELECT
pernyataan. Klausa OUTPUT menampilkan baris yang disisipkan ke EmployeeSales
dalam tabel. Perhatikan bahwa klausul ORDER BY dalam pernyataan SELECT tidak digunakan untuk menentukan 5 karyawan teratas.
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
YearlySales money NOT NULL
);
GO
INSERT TOP(5)INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName,
inserted.LastName, inserted.YearlySales
SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
Jika Anda harus menggunakan TOP untuk menyisipkan baris dalam urutan kronologis yang bermakna, Anda harus menggunakan TOP bersama dengan ORDER BY dalam pernyataan subpilih seperti yang ditunjukkan dalam contoh berikut. Klausa OUTPUT menampilkan baris yang disisipkan ke EmployeeSales
dalam tabel. Perhatikan bahwa 5 karyawan teratas sekarang dimasukkan berdasarkan hasil klausul ORDER BY alih-alih baris acak.
INSERT INTO dbo.EmployeeSales
OUTPUT inserted.EmployeeID, inserted.FirstName,
inserted.LastName, inserted.YearlySales
SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
Menentukan Objek Target Selain Tabel Standar
Contoh di bagian ini menunjukkan cara menyisipkan baris dengan menentukan variabel tampilan atau tabel.
K. Menyisipkan data dengan menentukan tampilan
Contoh berikut menentukan nama tampilan sebagai objek target; namun, baris baru disisipkan dalam tabel dasar yang mendasar. Urutan nilai dalam INSERT
pernyataan harus cocok dengan urutan kolom tampilan. Untuk informasi selengkapnya, lihat Mengubah Data Melalui Tampilan.
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS
SELECT column_2, column_1
FROM T1;
GO
INSERT INTO V1
VALUES ('Row 1',1);
GO
SELECT column_1, column_2
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO
L. Menyisipkan data ke dalam variabel tabel
Contoh berikut menentukan variabel tabel sebagai objek target dalam database AdventureWorks2022.
-- Create the table variable.
DECLARE @MyTableVar table(
LocationID int NOT NULL,
CostRate smallmoney NOT NULL,
NewCostRate AS CostRate * 1.5,
ModifiedDate datetime);
-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
SELECT LocationID, CostRate, GETDATE()
FROM Production.Location
WHERE CostRate > 0;
-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO
Menyisipkan Baris ke dalam Tabel Jarak Jauh
Contoh di bagian ini menunjukkan cara menyisipkan baris ke dalam tabel target jarak jauh dengan menggunakan server tertaut atau fungsi set baris untuk mereferensikan tabel jarak jauh.
M. Menyisipkan data ke dalam tabel jarak jauh dengan menggunakan server tertaut
Contoh berikut menyisipkan baris ke dalam tabel jarak jauh. Contoh dimulai dengan membuat tautan ke sumber data jarak jauh dengan menggunakan sp_addlinkedserver. Nama server tertaut, MyLinkServer
, kemudian ditentukan sebagai bagian dari nama objek empat bagian dalam form server.catalog.schema.object.
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name'
-- or 'server_nameinstance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'AdventureWorks2022';
GO
-- Specify the remote data source in the FROM clause using a four-part name
-- in the form linked_server.catalog.schema.object.
INSERT INTO MyLinkServer.AdventureWorks2022.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO
N. Menyisipkan data ke dalam tabel jarak jauh dengan menggunakan fungsi OPENQUERY
Contoh berikut menyisipkan baris ke dalam tabel jarak jauh dengan menentukan fungsi set baris OPENQUERY . Nama server tertaut yang dibuat dalam contoh sebelumnya digunakan dalam contoh ini.
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
INSERT OPENQUERY (MyLinkServer,
'SELECT Name, GroupName
FROM AdventureWorks2022.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO
O. Menyisipkan data ke dalam tabel jarak jauh dengan menggunakan fungsi OPENDATASOURCE
Contoh berikut menyisipkan baris ke dalam tabel jarak jauh dengan menentukan fungsi set baris OPENDATASOURCE . Tentukan nama server yang valid untuk sumber data dengan menggunakan format server_name atau server_name\instance_name.
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format
-- server_name or server_nameinstance_name.
INSERT INTO OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2022.HumanResources.Department (Name, GroupName)
VALUES (N'Standards and Methods', 'Quality Assurance');
GO
P. Menyisipkan ke dalam tabel eksternal yang dibuat menggunakan PolyBase
Ekspor data dari SQL Server ke Hadoop atau Azure Storage. Pertama, buat tabel eksternal yang menunjuk ke file atau direktori tujuan. Kemudian, gunakan INSERT INTO untuk mengekspor data dari tabel SQL Server lokal ke sumber data eksternal. Pernyataan INSERT INTO membuat file atau direktori tujuan jika tidak ada dan hasil pernyataan SELECT diekspor ke lokasi yang ditentukan dalam format file yang ditentukan. Untuk informasi selengkapnya, lihat Mulai menggunakan PolyBase.
Berlaku untuk: SQL Server.
-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (
[FirstName] char(25) NOT NULL,
[LastName] char(25) NOT NULL,
[YearlyIncome] float NULL,
[MaritalStatus] char(1) NOT NULL
)
WITH (
LOCATION='/old_data/2009/customerdata.tbl',
DATA_SOURCE = HadoopHDP2,
FILE_FORMAT = TextFileFormat,
REJECT_TYPE = VALUE,
REJECT_VALUE = 0
);
-- Export data: Move old data to Hadoop while keeping
-- it query-able via external table.
INSERT INTO dbo.FastCustomer2009
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2
ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;
Memuat Data Secara Massal dari Tabel atau File Data
Contoh di bagian ini menunjukkan dua metode untuk memuat data secara massal ke dalam tabel dengan menggunakan pernyataan INSERT.
T. Menyisipkan data ke dalam tumpukan dengan pengelogan minimal
Contoh berikut membuat tabel baru (tumpukan) dan menyisipkan data dari tabel lain ke dalamnya menggunakan pengelogan minimal. Contoh mengasumsikan bahwa model AdventureWorks2022
pemulihan database diatur ke FULL. Untuk memastikan pengelogan minimal digunakan, model AdventureWorks2022
pemulihan database diatur ke BULK_LOGGED sebelum baris disisipkan dan diatur ulang ke PENUH setelah INSERT INTO... Pernyataan SELECT. Selain itu, petunjuk TABLOCK ditentukan untuk tabel Sales.SalesHistory
target . Ini memastikan bahwa pernyataan menggunakan ruang minimal dalam log transaksi dan berkinerja efisien.
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
SalesOrderID int NOT NULL,
SalesOrderDetailID int NOT NULL,
CarrierTrackingNumber nvarchar(25) NULL,
OrderQty smallint NOT NULL,
ProductID int NOT NULL,
SpecialOfferID int NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money NOT NULL,
LineTotal money NOT NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL,
ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks2022
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
(SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
LineTotal,
rowguid,
ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO
R. Menggunakan fungsi OPENROWSET dengan MASSAL untuk memuat data secara massal ke dalam tabel
Contoh berikut menyisipkan baris dari file data ke dalam tabel dengan menentukan fungsi OPENROWSET. Petunjuk tabel IGNORE_TRIGGERS ditentukan untuk pengoptimalan performa. Untuk contoh selengkapnya, lihat Mengimpor Data Massal dengan Menggunakan SISIPAN MASSAL atau OPENROWSET(MASSAL...) (SQL Server).
Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName
FROM OPENROWSET (
BULK 'C:SQLFilesDepartmentData.txt',
FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',
ROWS_PER_BATCH = 15000)AS b ;
Mengesampingkan Perilaku Default Pengoptimal Kueri dengan Menggunakan Petunjuk
Contoh di bagian ini menunjukkan cara menggunakan petunjuk tabel untuk mengesampingkan perilaku default pengoptimal kueri untuk sementara waktu saat memproses pernyataan INSERT.
Perhatian
Karena pengoptimal kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, kami menyarankan agar petunjuk hanya digunakan sebagai upaya terakhir oleh pengembang dan administrator database yang berpengalaman.
S. Menggunakan petunjuk TABLOCK untuk menentukan metode penguncian
Contoh berikut menentukan bahwa kunci eksklusif (X) diambil pada tabel Production.Location dan disimpan hingga akhir pernyataan INSERT.
Berlaku untuk: SQL Server, SQL Database.
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
Menangkap Hasil Pernyataan INSERT
Contoh di bagian ini menunjukkan cara menggunakan Klausul OUTPUT untuk mengembalikan informasi dari, atau ekspresi berdasarkan, setiap baris yang terpengaruh oleh pernyataan INSERT. Hasil ini dapat dikembalikan ke aplikasi pemrosesan untuk digunakan dalam hal-hal seperti pesan konfirmasi, pengarsipan, dan persyaratan aplikasi lainnya.
T. Menggunakan OUTPUT dengan pernyataan INSERT
Contoh berikut menyisipkan baris ke ScrapReason
dalam tabel dan menggunakan klausul OUTPUT
untuk mengembalikan hasil pernyataan ke @MyTableVar
variabel tabel. ScrapReasonID
Karena kolom ditentukan dengan IDENTITY
properti, nilai tidak ditentukan dalam pernyataan untuk kolom tersebutINSERT
. Namun, perhatikan bahwa nilai yang dihasilkan oleh Mesin Database untuk kolom tersebut OUTPUT
dikembalikan dalam klausa di INSERTED.ScrapReasonID
kolom .
DECLARE @MyTableVar table( NewScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
U. Menggunakan OUTPUT dengan identitas dan kolom komputasi
Contoh berikut membuat EmployeeSales
tabel lalu menyisipkan beberapa baris ke dalamnya menggunakan pernyataan INSERT dengan pernyataan SELECT untuk mengambil data dari tabel sumber. Tabel EmployeeSales
berisi kolom identitas (EmployeeID
) dan kolom komputasi (ProjectedSales
). Karena nilai-nilai ini dihasilkan oleh Mesin Database selama operasi penyisipan, tidak satu pun dari kolom ini yang dapat didefinisikan dalam @MyTableVar
.
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
V. Menyisipkan data yang dikembalikan dari klausa OUTPUT
Contoh berikut mengambil data yang dikembalikan dari klausul OUTPUT pernyataan MERGE, dan menyisipkan data tersebut ke dalam tabel lain. Pernyataan MERGE memperbarui Quantity
kolom ProductInventory
tabel setiap hari, berdasarkan pesanan yang diproses dalam SalesOrderDetail
tabel dalam database AdventureWorks2022. Ini juga menghapus baris untuk produk yang inventorinya turun ke 0. Contoh mengambil baris yang dihapus dan menyisipkannya ke dalam tabel lain, ZeroInventory
, yang melacak produk tanpa inventori.
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = '20070401'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;
W. Menyisipkan data menggunakan opsi SELECT
Contoh berikut menunjukkan cara menyisipkan beberapa baris data menggunakan pernyataan INSERT dengan opsi SELECT. Pernyataan pertama INSERT
menggunakan SELECT
pernyataan secara langsung untuk mengambil data dari tabel sumber, lalu untuk menyimpan tataan hasil dalam EmployeeTitles
tabel.
CREATE TABLE EmployeeTitles
( EmployeeKey INT NOT NULL,
LastName varchar(40) NOT NULL,
Title varchar(50) NOT NULL
);
INSERT INTO EmployeeTitles
SELECT EmployeeKey, LastName, Title
FROM ssawPDW.dbo.DimEmployee
WHERE EndDate IS NULL;
X. Menentukan label dengan pernyataan INSERT
Contoh berikut menunjukkan penggunaan label dengan pernyataan INSERT.
-- Uses AdventureWorks
INSERT INTO DimCurrency
VALUES (500, N'C1', N'Currency1')
OPTION ( LABEL = N'label1' );
Y. Menggunakan label dan petunjuk kueri dengan pernyataan INSERT
Kueri ini memperlihatkan sintaks dasar untuk menggunakan label dan petunjuk gabungan kueri dengan pernyataan INSERT. Setelah kueri dikirimkan ke simpul Kontrol, SQL Server, yang berjalan pada simpul Komputasi, akan menerapkan strategi gabungan hash saat menghasilkan rencana kueri SQL Server. Untuk informasi selengkapnya tentang petunjuk gabungan dan cara menggunakan klausa OPTION, lihat OPTION (SQL Server PDW).
-- Uses AdventureWorks
INSERT INTO DimCustomer (CustomerKey, CustomerAlternateKey,
FirstName, MiddleName, LastName )
SELECT ProspectiveBuyerKey, ProspectAlternateKey,
FirstName, MiddleName, LastName
FROM ProspectiveBuyer p JOIN DimGeography g ON p.PostalCode = g.PostalCode
WHERE g.CountryRegionCode = 'FR'
OPTION ( LABEL = 'Add French Prospects', HASH JOIN);
Lihat Juga
SISIPAN MASSAL (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
FROM (Transact-SQL)
IDENTITY (Properti) (Transact-SQL)
NEWID (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)
MERGE (Transact-SQL)
Klausul OUTPUT (Transact-SQL)
Menggunakan Tabel yang disisipkan dan dihapus