Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
Здравствуйте.

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

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


В MS SQL на такой запрос стоит ограничение в 1000 записей. Данные( строки ) передаются методом POST из textarea, Framework Yii2. Как я понял BULK INSERT позволяет делать добавление данных только из файла. Как можно решить данную проблему?
11 ноя 19, 09:52    [22013268]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
L_argo
Member

Откуда:
Сообщений: 1030
Разбивать по одной тыще.
11 ноя 19, 09:55    [22013273]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Андрей Юниор
Member

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


https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
11 ноя 19, 10:17    [22013303]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
invm
Member

Откуда: Москва
Сообщений: 8870
_BBC_,

INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);
11 ноя 19, 10:29    [22013315]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4103
_BBC_,

А что такое BULK INSERT вы смотрели? Он вам не подойдет?
11 ноя 19, 11:21    [22013351]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4103
_BBC_,

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

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8
11 ноя 19, 11:22    [22013352]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
L_argo
Разбивать по одной тыще.

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

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


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

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

invm
_BBC_,

INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);

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

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

a_voronin
_BBC_,

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

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


Спасибо всем за ответы.
11 ноя 19, 11:37    [22013368]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Андрей Юниор
Member

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

На самом деле ничего сложного. На стороне MS SQL:
-- создаём тип
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).
11 ноя 19, 11:55    [22013386]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
iap
Member

Откуда: Москва
Сообщений: 46907
invm
_BBC_,

INSERT INTO tableName (id, name) 
select
 id, name
from
 (VALUES 
  ('1','Первая строка'), 
  ('2','Вторая строка'), 
  ('3','Третья строка')
  ............................................
  ('N','N строка')
 ) t(id, name);
Если не ошибаюсь, указанное ограничение действует на конструктор значений VALUES (...),(...),...
Поэтому чем этот вариант отличается от исходного?
Вот UNION ALL такого ограничения не имеет (может, оно и есть, но существенно больше 1000).
INSERT tableName (id, name)
          SELECT '1','Первая строка'
UNION ALL SELECT '2','Вторая строка'
UNION ALL SELECT '3','Третья строка'
  ............................................
UNION ALL SELECT 'N','N строка';
11 ноя 19, 12:26    [22013424]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
invm
Member

Откуда: Москва
Сообщений: 8870
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 строк.
11 ноя 19, 12:29    [22013428]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
iap
Member

Откуда: Москва
Сообщений: 46907
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 строк.
Спасибо
11 ноя 19, 12:38    [22013439]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

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

На самом деле ничего сложного. На стороне MS SQL:
-- создаём тип
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).


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

    $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.

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

 SELECT id FROM tableName WHERE id IN (1,2,3...N)


Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос?
11 ноя 19, 14:08    [22013554]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4480
_BBC_, на пачки разбивайте как ещё
11 ноя 19, 14:24    [22013583]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
L_argo
Member

Откуда:
Сообщений: 1030
Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос?
Кинуть их в отдельную (временную?) табличку и потом доставать оттуда.
11 ноя 19, 14:36    [22013600]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
L_argo
Как здесь можно обойти ограничение в 2100 параметров или как правильно формировать подобный запрос?
Кинуть их в отдельную (временную?) табличку и потом доставать оттуда.

Сделал вот так:
SELECT [detailNum] FROM [tbl_details] WHERE [detailNum] IN ( SELECT ([detailNum]) FROM ( VALUES ('A113001015'),('481H1007020'),('SMD184303').....('1002118E02') ) t ([detailNum]) )


Ну оно как бы работает.... то есть ошибок нет и результат верный.... НО - ВРЕМЯ ВЫПОЛНЕНИЯ ЗАПРОСА - просто космическое - около 20 секунд.... не тот результат, который хотелось бы видеть.... в MySQL с этой же БД подобный запрос типа
SELECT * FROM * WHERE field IN (values) 
выполняется за доли секунды и с проверкой 3к параметров в IN и с проверкой 10к параметров в IN... хотелось бы такой же результат получить и с MS SQL....
11 ноя 19, 14:51    [22013610]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36505
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.
11 ноя 19, 15:12    [22013626]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7014
_BBC_,

это не эффективно, надо выгружать в файл и загружать на сервер любым штатным ETL средством - Bulk Insert, SSIS.
11 ноя 19, 15:23    [22013636]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Андрей Юниор
Member

Откуда: Москва
Сообщений: 456
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.

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

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

SSIS для каких-то 3-10 тыс. строк - это сильно излишне. Зачем?
11 ноя 19, 15:25    [22013639]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.


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

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

А вот SELECT ... WHERE IN с множеством параметров - не понимаю как сделать.... точнее сделал как написал постом выше, но это оОчень долго выполняется. Тут идей нет((
11 ноя 19, 15:26    [22013641]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7014
Андрей Юниор,

Как Вы узнали, что у ТС всего 10000 строк? Я думаю, что он бы не волновался из-за такого количества.
11 ноя 19, 15:29    [22013647]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
Андрей Юниор
Гавриленко Сергей Алексеевич
Чем только люди не занимаются, чтобы только не закидывать данные на сервер балк-инсертом.

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

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

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

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

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


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

Собственно на MySQL есть работающий вариант, все прекрасно работает. Но в силу обстоятельств( не моего желания ) - нужно эту БД перенести на MS SQL - собственно сам перенос я сделал, таблицы, данные - все перенес. Но естественно теперь методы на сайте по работе с БД не работают, либо работают не так как нужно. Вот пытаюсь переписать потихоньку их. Так вот если вставка - это операция не самая частая и в принципе тот вариант, который сейчас заработал меня устраивает, то вот операция поиска по большому( до 100к строк ) довольно частая, имеется ввиду на входе имеем к примеру 60к строк и нужно ответом от БД получить, что из этого есть в БД. Вот как это правильно сделать?
11 ноя 19, 15:36    [22013653]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7014
_BBC_,

то есть планируете загружать 1 млн строк в одном пакете? Это 30-40 мегабайт?
11 ноя 19, 16:06    [22013681]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
Владислав Колосов
_BBC_,

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


Нет. На проверку( загрузка через textarea ) более 60к строк вряд ли будет.
11 ноя 19, 16:20    [22013701]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7014
_BBC_,

может как-то STRING_SPLIT() прикрутить...
12 ноя 19, 16:53    [22014633]     Ответить | Цитировать Сообщить модератору
 Re: Множественное добавление записей  [new]
_BBC_
Member

Откуда:
Сообщений: 8
Владислав Колосов
_BBC_,

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


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

..................
$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 );
.........................................
15 ноя 19, 21:21    [22017753]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить