ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Множественное добавление записей
25 сообщений из 25, страница 1 из 1
Множественное добавление записей
    #39887324
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Здравствуйте.

Подскажите, как можно в MS SQL сделать множественное добавление записей - в MySQL было так:

Код: sql
1.
2.
3.
4.
5.
6.
INSERT INTO tableName (id, name) VALUES 
('1','Первая строка'), 
('2','Вторая строка'), 
('3','Третья строка')
............................................
('N','N строка');



В MS SQL на такой запрос стоит ограничение в 1000 записей. Данные( строки ) передаются методом POST из textarea, Framework Yii2. Как я понял BULK INSERT позволяет делать добавление данных только из файла. Как можно решить данную проблему?
...
Рейтинг: 0 / 0
11.11.2019, 09:52
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887325
L_argo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
L_argo
Участник
Разбивать по одной тыще.
...
Рейтинг: 0 / 0
11.11.2019, 09:55
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887337
Андрей Юниор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Юниор
Участник
Сделать хранимую таблицу с табличным параметром (и табличный параметр тоже сделать), и все строки для вставки передавать в качестве параметра при вызове процедуры. Ну дальше
Код: sql
1.
insert into (...) select from @TVP



https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
...
Рейтинг: 0 / 0
11.11.2019, 10:17
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887341
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
_BBC_,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);
...
Рейтинг: 0 / 0
11.11.2019, 10:29
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887361
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
_BBC_,

А что такое BULK INSERT вы смотрели? Он вам не подойдет?
...
Рейтинг: 0 / 0
11.11.2019, 11:21
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887362
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
_BBC_,

BULK INSERT можно делать ещё из памяти. На .NET

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8
...
Рейтинг: 0 / 0
11.11.2019, 11:22
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887373
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
L_argo
Разбивать по одной тыще.

Как ни странно, но почему-то мне этот вариант сразу в голову не пришел. А ведь в моем случае он наверное один из самых простых. Практически не придется код переписывать, но есть и минус - будет несколько запросов к БД - чего не очень хотелось бы.

Андрей Юниор
Сделать хранимую таблицу с табличным параметром (и табличный параметр тоже сделать), и все строки для вставки передавать в качестве параметра при вызове процедуры. Ну дальше
Код: sql
1.
insert into (...) select from @TVP



https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

Если честно, боюсь в данный момент для меня это может быть трудным в реализации. Так как с MS SQL скажем так мало знаком пока. Да и не совсем понимаю как из Yii будут подобные обращения идти...

invm
_BBC_,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);


Если я правильно понимаю тут уже не будет ограничения на 1000 записей, если так, то наверное это самый удобный вариант для меня, останется только понять как сделать такой запрос из Yii. Так как сейчас запрос выполняется следующим образом:
Код: php
1.
Yii::$app->db->createCommand()->batchInsert(\app\models\Detail::tableName(), ['detailNum'], $detailsInDBArr)->execute();


И framework автоматически генерирует на основе этого запрос такого же вида, как в первом посте. Нужно будет как-то переписать это... Но тем не менее думаю, что это мой вариант.

a_voronin
_BBC_,

А что такое BULK INSERT вы смотрели? Он вам не подойдет?

Если бы был пример на php(Yii) - как из массива занести данные в БД через BULK INSERT, то может и подошел бы, а так видимо не мой вариант.


Спасибо всем за ответы.
...
Рейтинг: 0 / 0
11.11.2019, 11:37
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887387
Андрей Юниор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Юниор
Участник
_BBC_
Если честно, боюсь в данный момент для меня это может быть трудным в реализации. Так как с MS SQL скажем так мало знаком пока. Да и не совсем понимаю как из Yii будут подобные обращения идти...

На самом деле ничего сложного. На стороне MS SQL:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
-- создаём тип
CREATE TYPE SomeTVP AS TABLE
(
    id   INT,
    name NVARCHAR(MAX)
)
GO
-- Таблица
CREATE TABLE SomeTable
(
    id   INT,
    name NVARCHA(MAX)
)
GO
-- Процедура с параметром типа SomeTVP
CREATE PROCEDURE USP_SomeProcedure @TVP SomeTVP
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO SomeTable (id, name)
    SELECT id, name
    FROM @TVP
END
GO


Остаётся только вызвать хранимую процедуру с параметром типа SomeTVP - в принципе, это самое сложное, но это вопрос чисто php(Yii).
...
Рейтинг: 0 / 0
11.11.2019, 11:55
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887407
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
invm
_BBC_,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);

Если не ошибаюсь, указанное ограничение действует на конструктор значений VALUES (...),(...),...
Поэтому чем этот вариант отличается от исходного?
Вот UNION ALL такого ограничения не имеет (может, оно и есть, но существенно больше 1000).
Код: sql
1.
2.
3.
4.
5.
6.
INSERT tableName (id, name)
          SELECT '1','Первая строка'
UNION ALL SELECT '2','Вторая строка'
UNION ALL SELECT '3','Третья строка'
  ............................................
UNION ALL SELECT 'N','N строка';
...
Рейтинг: 0 / 0
11.11.2019, 12:26
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887410
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
iap
Если не ошибаюсь, указанное ограничение действует на конструктор значений VALUES (...),(...)
https://docs.microsoft.com/ru-ru/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15 При использовании в виде производной таблицы ограничение на количество строк отсутствует.
При использовании в виде предложения VALUES инструкции INSERT... VALUES применяется ограничение в размере 1000 строк.
...
Рейтинг: 0 / 0
11.11.2019, 12:29
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887415
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
invm
iap
Если не ошибаюсь, указанное ограничение действует на конструктор значений VALUES (...),(...)
https://docs.microsoft.com/ru-ru/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15 При использовании в виде производной таблицы ограничение на количество строк отсутствует.
При использовании в виде предложения VALUES инструкции INSERT... VALUES применяется ограничение в размере 1000 строк.
Спасибо
...
Рейтинг: 0 / 0
11.11.2019, 12:38
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887469
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Андрей Юниор
_BBC_
Если честно, боюсь в данный момент для меня это может быть трудным в реализации. Так как с MS SQL скажем так мало знаком пока. Да и не совсем понимаю как из Yii будут подобные обращения идти...

На самом деле ничего сложного. На стороне MS SQL:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
-- создаём тип
CREATE TYPE SomeTVP AS TABLE
(
    id   INT,
    name NVARCHAR(MAX)
)
GO
-- Таблица
CREATE TABLE SomeTable
(
    id   INT,
    name NVARCHA(MAX)
)
GO
-- Процедура с параметром типа SomeTVP
CREATE PROCEDURE USP_SomeProcedure @TVP SomeTVP
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO SomeTable (id, name)
    SELECT id, name
    FROM @TVP
END
GO


Остаётся только вызвать хранимую процедуру с параметром типа SomeTVP - в принципе, это самое сложное, но это вопрос чисто php(Yii).


Спасибо за пояснение. В принципе даже понял примерно как это делать, но пока остановился на варианте:

Код: php
1.
2.
3.
4.
5.
6.
7.
8.
    $sql = 'INSERT INTO [tbl_details] ([detailNum]) SELECT ([detailNum]) FROM ( VALUES {VALUES} ) t ([detailNum])';

    $values = '' ;
    foreach ( $detailsArr as $detail )
        $values .= "('" . $detail . "'),";

    if( count( $detailsArr ) > 0 )
       Yii::$app->db->createCommand()->setSql( str_replace("{VALUES}",substr($values,0,-1),$sql) )->execute();



Записи добавляет, на количество не ругается. Но теперь появилась новая проблема:

SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.

Это запрос вида:

Код: sql
1.
 SELECT id FROM tableName WHERE id IN (1,2,3...N)



Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос?
...
Рейтинг: 0 / 0
11.11.2019, 14:08
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887480
Фотография buser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
buser
Участник
_BBC_, на пачки разбивайте как ещё
...
Рейтинг: 0 / 0
11.11.2019, 14:24
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887492
L_argo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
L_argo
Участник
Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос? Кинуть их в отдельную (временную?) табличку и потом доставать оттуда.
...
Рейтинг: 0 / 0
11.11.2019, 14:36
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887499
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
L_argo
Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос?
Кинуть их в отдельную (временную?) табличку и потом доставать оттуда.
Сделал вот так:
Код: sql
1.
SELECT [detailNum] FROM [tbl_details] WHERE [detailNum] IN ( SELECT ([detailNum]) FROM ( VALUES ('A113001015'),('481H1007020'),('SMD184303').....('1002118E02') ) t ([detailNum]) )



Ну оно как бы работает.... то есть ошибок нет и результат верный.... НО - ВРЕМЯ ВЫПОЛНЕНИЯ ЗАПРОСА - просто космическое - около 20 секунд.... не тот результат, который хотелось бы видеть.... в MySQL с этой же БД подобный запрос типа
Код: sql
1.
SELECT * FROM * WHERE field IN (values)

выполняется за доли секунды и с проверкой 3к параметров в IN и с проверкой 10к параметров в IN... хотелось бы такой же результат получить и с MS SQL....
...
Рейтинг: 0 / 0
11.11.2019, 14:51
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887508
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.
...
Рейтинг: 0 / 0
11.11.2019, 15:12
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887513
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
_BBC_,

это не эффективно, надо выгружать в файл и загружать на сервер любым штатным ETL средством - Bulk Insert, SSIS.
...
Рейтинг: 0 / 0
11.11.2019, 15:23
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887514
Андрей Юниор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Юниор
Участник
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.

Владислав Колосов
это не эффективно, надо выгружать в файл и загружать на сервер любым штатным ETL средством - Bulk Insert, SSIS.

Почему не ХП + TVP?

SSIS для каких-то 3-10 тыс. строк - это сильно излишне. Зачем?
...
Рейтинг: 0 / 0
11.11.2019, 15:25
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887516
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.


С INSERT конечно тоже хотелось бы узнать как из массива PHP занести данные в таблицу с помощью BULK INSERT - может подскажите?

Хотя в той вариации как я сейчас сделал оно работает и добавляет достаточно быстро - несколько миллисекунд.

А вот SELECT ... WHERE IN с множеством параметров - не понимаю как сделать.... точнее сделал как написал постом выше, но это оОчень долго выполняется. Тут идей нет((
...
Рейтинг: 0 / 0
11.11.2019, 15:26
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887519
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Андрей Юниор,

Как Вы узнали, что у ТС всего 10000 строк? Я думаю, что он бы не волновался из-за такого количества.
...
Рейтинг: 0 / 0
11.11.2019, 15:29
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887522
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Андрей Юниор
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.

Владислав Колосов
это не эффективно, надо выгружать в файл и загружать на сервер любым штатным ETL средством - Bulk Insert, SSIS.

Почему не ХП + TVP?

SSIS для каких-то 3-10 тыс. строк - это сильно излишне. Зачем?

Владислав Колосов
Андрей Юниор,

Как Вы узнали, что у ТС всего 10000 строк? Я думаю, что он бы не волновался из-за такого количества.


Строк примерно 100 тысяч изначально. В перспективе за небольшой промежуток времени дойдет до 1-2 млн, на этом уже остановится - то есть далее изменения будут незначительные.

Собственно на MySQL есть работающий вариант, все прекрасно работает. Но в силу обстоятельств( не моего желания ) - нужно эту БД перенести на MS SQL - собственно сам перенос я сделал, таблицы, данные - все перенес. Но естественно теперь методы на сайте по работе с БД не работают, либо работают не так как нужно. Вот пытаюсь переписать потихоньку их. Так вот если вставка - это операция не самая частая и в принципе тот вариант, который сейчас заработал меня устраивает, то вот операция поиска по большому( до 100к строк ) довольно частая, имеется ввиду на входе имеем к примеру 60к строк и нужно ответом от БД получить, что из этого есть в БД. Вот как это правильно сделать?
...
Рейтинг: 0 / 0
11.11.2019, 15:36
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887543
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
_BBC_,

то есть планируете загружать 1 млн строк в одном пакете? Это 30-40 мегабайт?
...
Рейтинг: 0 / 0
11.11.2019, 16:06
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39887558
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Владислав Колосов
_BBC_,

то есть планируете загружать 1 млн строк в одном пакете? Это 30-40 мегабайт?


Нет. На проверку( загрузка через textarea ) более 60к строк вряд ли будет.
...
Рейтинг: 0 / 0
11.11.2019, 16:20
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39888163
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
_BBC_,

может как-то STRING_SPLIT() прикрутить...
...
Рейтинг: 0 / 0
12.11.2019, 16:53
| Ответить | Цитировать | Написать  
Множественное добавление записей
    #39889943
_BBC_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_BBC_
Гость
Владислав Колосов
_BBC_,

может как-то STRING_SPLIT() прикрутить...


В итоге решил все достаточно просто, не знаю на сколько верно конечно, но работает быстро:

Код: php
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
..................
$values = implode( "\r\n" , $details );

    if( $values != "" ) {

        $tempFileName = writeTempFile( $values );

        $sql = "SELECT [detailNum] FROM [tbl_details] WHERE [detailNum] IN ( 
                SELECT [detailNum] FROM OPENROWSET(
                   BULK  '{$tempFileName}',
                   FORMATFILE = '.........\SQL_formatFiles\detailNum.fmt' ) AS a )";

        $detailsInDB = Yii::$app->db->createCommand()->setSql($sql)->queryAll();

        unlink( $tempFileName );
.........................................
...
Рейтинг: 0 / 0
15.11.2019, 21:21
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Множественное добавление записей
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]