Wstawić wiele wierszy BEZ powtarzania części „INSERT INTO…” w instrukcji?


536

Wiem, że zrobiłem to przed laty, ale nie pamiętam składni i nie mogę jej nigdzie znaleźć z powodu zebrania mnóstwa dokumentów pomocy i artykułów na temat „importu zbiorczego”.

Oto, co chcę zrobić, ale składnia jest nieprawidłowa ... proszę, ktoś, kto to zrobił wcześniej, pomóż mi :)

INSERT INTO dbo.MyTable (ID, Name)
VALUES (123, 'Timmy'),
    (124, 'Jonny'),
    (125, 'Sally')

Wiem, że jest to bliskie właściwej składni. Może potrzebuję tam słowa „DUŻO” lub czegoś, czego nie pamiętam. Dowolny pomysł?

Potrzebuję tego do bazy danych SQL Server 2005. Próbowałem tego kodu, ale bezskutecznie:

DECLARE @blah TABLE
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

INSERT INTO @blah (ID, Name)
    VALUES (123, 'Timmy')
    VALUES (124, 'Jonny')
    VALUES (125, 'Sally')

SELECT * FROM @blah

Dostaję Incorrect syntax near the keyword 'VALUES'.


4
Twój powyższy kod jest w porządku, wystarczy dodać instrukcję „, po wartości”
sam

4
INSERT INTO @blah (ID, Name), VALUES (123, „Timmy”), VALUES (124, „Jonny”), VALUES (125, „Sally”)
sam

1
Uwaga: możesz wstawić do 1000 wierszy tylko tą metodą. WSTAW WARTOŚCI #Test (LWPurchaseOrderID) WARTOŚCI (935791), (935933)
Anoop Verma

16
2005 nie jest już obsługiwany. W 2008, 2012 i 2016 roku możesz prawie użyć tego, co INSERT INTO @blah (ID, Name) VALUES (123, 'Timmy'), (124, 'Jonny'), (125, 'Sally') wpisujesz „WARTOŚCI” pojawiają się tylko raz i potrzebujesz przecinków między zestawami.
J. Chris Compton

Odpowiedzi:


328
INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

W przypadku SQL Server 2008 można to zrobić w jednej klauzuli VALUES dokładnie tak , jak w instrukcji w pytaniu (wystarczy dodać przecinek, aby oddzielić każdą instrukcję wartości) ...


10
Czy jest to bardziej wydajne niż używanie wielu instrukcji INSERT?
Code Commander

7
@Code Commander: nie, ponieważ kompilacja jest dłuższa. Tak, ponieważ masz tylko jedną wkładkę. Ale odpowiada na pytanie: brak powtórzeniaINSERT table (columnlist)
gbn

3
@VoidKing Wiem, że przychodzi to pół roku później i być może już to odkryłeś już dawno, ale to naprawdę bardzo proste. Za pomocą selectmożesz utworzyć zestaw z kolumnami i wierszami, a zgodnie z projektem rzędy te można insertedytować w innej tabeli z taką samą ilością kolumn. Możesz nawet użyć kombinacji literałów i wartości. Na przykład użycie insertz select 'A', ID from ATablespowoduje wstawienie „A” w pierwszej kolumnie za każdym razem, a wartość kolumny ID odpowiedniego wiersza ATable w drugiej kolumnie.
MarioDS

1
Działa to również z bazą danych DB2, która jest ważnym sidenotem dla tych, którzy tkwią w przestarzałej technologii. Wartości rozdzielone przecinkami są dla mnie lepsze dla tych, którzy pracują w SQL Server 2008 lub nowszym. OP może usunąć wszystkie „wartości” oprócz pierwszej i zastąpić je,
JPK

1
@PRMan nie zrobiłbyś tego po wersji SQL Server 2008. Jak wspomniano ...
gbn

510

Twoja składnia prawie działa w SQL Server 2008 (ale nie w SQL Server 2005 1 ):

CREATE TABLE MyTable (id int, name char(10));

INSERT INTO MyTable (id, name) VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe');

SELECT * FROM MyTable;

id |  name
---+---------
1  |  Bob       
2  |  Peter     
3  |  Joe       

1 Po udzieleniu odpowiedzi na pytanie nie było oczywiste, że pytanie dotyczyło programu SQL Server 2005. Pozostawiam tę odpowiedź tutaj, ponieważ uważam, że jest ona nadal aktualna.


Działa w SQL Server 2012
user2601995

27
Server 2008 nie zezwala na wstawianie w ten sposób więcej niż 1000 wierszy.
Michael - Where's Clay Shirky

1
Co się stanie, jeśli jeden zestaw wartości jest wadliwy? Czy wszystkie płytki zostaną wycofane, czy tylko wadliwy rząd?
netblognet

2
@netblognet Właśnie przetestowałem, że tylko błędne wiersze nie są wstawiane (wszystkie pozostałe są wstawiane poprawnie)
Mauricio Gracia Gutierrez

1
@Michael To może być zniesione stackoverflow.com/a/42703601/5070879
Łukasz Szozda

243

Jeśli Twoje dane są już w bazie danych, możesz:

INSERT INTO MyTable(ID, Name)
SELECT ID, NAME FROM OtherTable

Jeśli potrzebujesz na stałe zakodować dane, wówczas SQL 2008 i nowsze wersje pozwalają wykonać następujące czynności ...

INSERT INTO MyTable (Name, ID)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

28

Używając INSERT INTO ... VALUESskładni jak w odpowiedzi Daniela Vassallo, istnieje jedno irytujące ograniczenie:

Z MSDN

Maksymalna liczba wierszy, które można zbudować przez wstawienie wierszy bezpośrednio na liście WARTOŚCI wynosi 1000

Najprostszym sposobem na ominięcie tego ograniczenia jest użycie tabeli pochodnej, takiej jak:

INSERT INTO dbo.Mytable(ID, Name)
SELECT ID, Name 
FROM (
   VALUES (1, 'a'),
          (2, 'b'),
          --...
          -- more than 1000 rows
)sub (ID, Name);

LiveDemo


Działa to począwszy od SQL Server 2008+


Czy mogę dostać link do artykułu na temat tej składni „sub”.
CodeCamper

2
@CodeCamper docs.microsoft.com/en-us/sql/t-sql/queries/… sekcja:C. Specifying multiple values as a derived table in a FROM clause
Łukasz Szozda

3
Zaletą tej odpowiedzi jest to, że umożliwia ona określenie identycznych wartości bez ich powtarzania (tego właśnie szukałem). Np. Z trzecią kolumną, która jest identyczna, nie trzeba powtarzać jej tysiąc razy.
Vadim Berman,

1
@VadimBerman Tak, to dobry scenariusz, gdy w tabeli nie zdefiniowano wartości domyślnej.
Łukasz Szozda

14

Możesz to zrobić (brzydkie, ale działa):

INSERT INTO dbo.MyTable (ID, Name) 
select * from
(
 select 123, 'Timmy'
  union all
 select 124, 'Jonny' 
  union all
 select 125, 'Sally'
 ...
) x

10

Osiągnie to, o co pytasz:

INSERT INTO table1 (ID, Name)
    VALUES (123, 'Timmy'), 
           (124, 'Jonny'), 
           (125, 'Sally');

Dla przyszłych programistów możesz również wstawić z innej tabeli :

INSERT INTO table1 (ID, Name)
    SELECT 
         ID, 
         Name 
    FROM table2

Lub nawet z wielu tabel :

INSERT INTO table1 (column2, column3)
    SELECT 
         t2.column, 
         t3.column
    FROM table2 t2
         INNER JOIN table3 t3
         ON t2.ID = t3.ID

8

Możesz użyć związku:

INSERT INTO dbo.MyTable (ID, Name) 
SELECT ID, Name FROM (
    SELECT 123, 'Timmy'
    UNION ALL
    SELECT 124, 'Jonny'
    UNION ALL
    SELECT 125, 'Sally'
) AS X (ID, Name)

6

Wygląda to OK dla SQL Server 2008. W przypadku SS2005 i wcześniejszych należy powtórzyć instrukcję VALUES.

INSERT INTO dbo.MyTable (ID, Name)  
VALUES (123, 'Timmy')  
VALUES (124, 'Jonny')   
VALUES (125, 'Sally')  

EDYCJA :: Mój zły. Musisz powtórzyć „INSERT INTO” dla każdego wiersza w SS2005.

INSERT INTO dbo.MyTable (ID, Name)  
VALUES (123, 'Timmy')  
INSERT INTO dbo.MyTable (ID, Name)  
VALUES (124, 'Jonny')   
INSERT INTO dbo.MyTable (ID, Name)  
VALUES (125, 'Sally')  

6

Łatwiej byłoby używać XML w SQL Server do wstawiania wielu wierszy, w przeciwnym razie staje się to bardzo uciążliwe.

Zobacz pełny artykuł z objaśnieniami do kodu tutaj http://www.cyberminds.co.uk/blog/articles/how-to-insert-multiple-rows-in-sql-server.aspx

Skopiuj poniższy kod na serwer SQL, aby wyświetlić próbkę.

declare @test nvarchar(max)

set @test = '<topic><dialog id="1" answerId="41">
        <comment>comment 1</comment>
        </dialog>
    <dialog id="2" answerId="42" >
    <comment>comment 2</comment>
        </dialog>
    <dialog id="3" answerId="43" >
    <comment>comment 3</comment>
        </dialog>
    </topic>'

declare @testxml xml
set @testxml = cast(@test as xml)
declare @answerTemp Table(dialogid int, answerid int, comment varchar(1000))

insert @answerTemp
SELECT  ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') as ParamValues(ID)

6
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

LUB MOŻESZ UŻYWAĆ INNEGO SPOSOBU

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES 
('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

6

Korzystałem z następujących:

INSERT INTO [TableName] (ID, Name)
values (NEWID(), NEWID())
GO 10

Dodaje dziesięć wierszy z unikalnymi identyfikatorami GUID dla identyfikatora i nazwy.

Uwaga: nie kończ ostatniego wiersza (GO 10) znakiem „;” ponieważ spowoduje to błąd: wystąpił krytyczny błąd skryptu. Napotkano niepoprawną składnię podczas analizowania GO.



2

Działa to bardzo szybko i wydajnie w SQL. Załóżmy, że masz stolik Sample with 4 column a,b,c,d where a,b,d are int and c column is Varchar(50).

CREATE TABLE [dbo].[Sample](
[a] [int] NULL,
[b] [int] NULL,
[c] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[D] [int] NULL
)

Nie można wstawić wielu rekordów w tej tabeli przy użyciu następującego zapytania bez powtarzania instrukcji insert,

DECLARE @LIST VARCHAR(MAX)
SET @LIST='SELECT 1, 1, ''Charan Ghate'',11
     SELECT 2,2, ''Mahesh More'',12
     SELECT 3,3,''Mahesh Nikam'',13
     SELECT 4,4, ''Jay Kadam'',14'
INSERT SAMPLE (a, b, c,d) EXEC(@LIST)

Również przy użyciu C # SqlBulkCopy bulkcopy = new SqlBulkCopy(con)

Możesz wstawić 10 wierszy jednocześnie

   DataTable dt = new DataTable();
        dt.Columns.Add("a");
        dt.Columns.Add("b");
        dt.Columns.Add("c");
        dt.Columns.Add("d");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dt.NewRow();
            dr["a"] = 1;
            dr["b"] = 2;
            dr["c"] = "Charan";
            dr["d"] = 4;
            dt.Rows.Add(dr);
        }
        SqlConnection con = new SqlConnection("Connection String");
        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con))
        {
            con.Open();
            bulkcopy.DestinationTableName = "Sample";
            bulkcopy.WriteToServer(dt);
            con.Close();
        }

0

Oracle SQL Server Wstaw wiele wierszy

We wstawce wielozadaniowej wstawiasz wiersze obliczone na podstawie wierszy zwróconych z oceny podzapytania do jednej lub więcej tabel.

Bezwarunkowe WSTAW WSZYSTKIE : - Aby dodać wiele wierszy do tabeli jednocześnie, użyj następującej formy instrukcji INSERT:

INSERT ALL
   INTO table_name (column_list) VALUES (value_list_1)
   INTO table_name (column_list) VALUES (value_list_2)
   INTO table_name (column_list) VALUES (value_list_3)
   ...
   INTO table_name (column_list) VALUES (value_list_n)
SELECT 1 FROM DUAL; -- SubQuery

Podaj WSZYSTKIE, a następnie wiele klauzul insert_into_clauses, aby wykonać bezwarunkowe wstawianie wielozadaniowe. Baza danych Oracle wykonuje każdą klauzulę insert_into_clause jeden raz dla każdego wiersza zwróconego przez podzapytanie.

Serwer MySQL Wstaw wiele wierszy

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Zapytanie dotyczące wstawiania w jednym wierszu

INSERT INTO table_name (col1,col2) VALUES(val1,val2);

0

Inni tutaj sugerowali kilka składni z wieloma rekordami. Wyjaśniając to, sugeruję, aby najpierw wstawić do tabeli tymczasowej, a następnie wstawić główny stół.

Powodem tego jest ładowanie danych z zapytania może potrwać dłużej, a może to spowodować zablokowanie tabeli lub stron dłużej niż jest to konieczne, co spowalnia inne zapytania działające względem tej tabeli.

-- Make a temp table with the needed columns
select top 0 *
into #temp
from MyTable (nolock)

-- load data into it at your leisure (nobody else is waiting for this table or these pages)
insert #temp (ID, Name)
values (123, 'Timmy'),
(124, 'Jonny'),
(125, 'Sally')

-- Now that all the data is in SQL, copy it over to the real table. This runs much faster in most cases.
insert MyTable (ID, Name)
select ID, Name
from #temp

-- cleanup
drop table #temp

Ponadto twoje identyfikatory powinny być prawdopodobnie tożsamością (1,1) i prawdopodobnie nie powinieneś ich wstawiać, w zdecydowanej większości przypadków. Pozwól, aby SQL zdecydował za Ciebie.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.