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

Откуда:
Сообщений: 9
Стоит следующая задача: сделать вставку данных из текстового файла, лежащего на 1-ом sql server-е в таблицу расположенном на 2-ом sql server-e, который прилинкован к 1-ому. Оба sql server-а находятся на разных физических серверах.
-- Попытка сделать bulk insert завершилась неудачей: bulk insert не видит присоединенный сервер и выдает ошибку.
-- Попробовал использовать конструкцию INSERT INTO ... SELECT * FROM OPENROWSET (BULK...), но выяснилось что в опциях этой команды отсутствует возможность указать разделители столбцов и строк в загружаемом файле (а файл имеет четкую структуру). Соответственно вставка происходит тоже с ошибкой.
Подскажите пожалуйста как решить эту задачу? Может быть я упустил какой-то из вариантов из виду? Или не правильно воспользовался приведенными выше методами?
27 авг 12, 11:13    [13068061]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
use formatfile
Guest
>>>...но выяснилось что в опциях этой команды отсутствует возможность ...

OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
27 авг 12, 11:18    [13068094]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Спасибо! Действительно это вариант.
Но тогда возникает проблема формирования FORMATFILE. Делать этот файл вручную не представляется возможным т.к. предполагается грузить много разных по структуре файлов.
Есть ли возможность создавать этот файл автоматически с помощью какой-нибудь команды?
27 авг 12, 11:49    [13068321]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
dzerkach
Спасибо! Действительно это вариант.
Но тогда возникает проблема формирования FORMATFILE. Делать этот файл вручную не представляется возможным т.к. предполагается грузить много разных по структуре файлов.
Есть ли возможность создавать этот файл автоматически с помощью какой-нибудь команды?
Вы же не сможете грузить содержимое, не зная структуру, вам нужно в любом случае сделать таблицу правильной структуры. Вот на этом этапе нужно формировать формат-файл.

Вы сразу задачу раскрывайте целиком, а то вначале было:
dzerkach
Стоит следующая задача: сделать вставку данных из текстового файла, лежащего на 1-ом sql server-е в таблицу расположенном на 2-ом sql server-e, который прилинкован к 1-ому
а теперь уже что то другое...
27 авг 12, 13:50    [13069504]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
А таблицы правильной структуры для каждого файла у меня имеются. Так что осталось разобраться, как по таблице FORMATFILE состряпать. А это вроде с помощью команды BCP можно сделать.
27 авг 12, 18:04    [13071804]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
use formatfile
Guest
BOL - наше все:
To use bcp with the format option to create this format file, at the Windows command prompt, enter:
 
 bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T
27 авг 12, 18:16    [13071866]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
dzerkach
Подскажите пожалуйста как решить эту задачу? Может быть я упустил какой-то из вариантов из виду? Или не правильно воспользовался приведенными выше методами?


SSIS->Data Flow Task

Сообщение было отредактировано: 27 авг 12, 20:43
27 авг 12, 20:43    [13072395]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Эх! Незадача! Уже и FORMATFILE научился делать и OPENROWSET (BULK...) уже заработал.
Но возникает ошибка INSERT INTO [таблица линкованного сервера] SELECT * FROM OPENROWSET (BULK...) -- не видит линкованный сервер.
Причем команда INSERT INTO [таблица линкованного сервера] SELECT * FROM [таблица локального сервера] исполняется успешно.
В чем же дело?
29 авг 12, 18:13    [13084528]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
dzerkach
Эх! Незадача! Уже и FORMATFILE научился делать и OPENROWSET (BULK...) уже заработал.
Но возникает ошибка INSERT INTO [таблица линкованного сервера] SELECT * FROM OPENROWSET (BULK...) -- не видит линкованный сервер.
Причем команда INSERT INTO [таблица линкованного сервера] SELECT * FROM [таблица локального сервера] исполняется успешно.
В чем же дело?


Прям так и пишет - "не вижу"?
29 авг 12, 18:21    [13084593]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Ошибка следующего содержания:
Invalid object name '[имя линкованного сервера].[имя базы].[dbo].[имя таблицы]'.
И что с этим делать непонятно.
30 авг 12, 15:57    [13089633]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Так еще и не разобрался почему не работает...
Может кто-то все-таки знает в чем же здесь дело?
5 сен 12, 18:01    [13119785]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2995
dzerkach
Ошибка следующего содержания:
Invalid object name '[имя линкованного сервера].[имя базы].[dbo].[имя таблицы]'.
И что с этим делать непонятно.


а доступ к этой таблице есть?
а то по ошибке получается, что объекта нет или нет прав
выполните запрос
select * from [имя линкованного сервера].[имя базы].[dbo].[имя таблицы]
6 сен 12, 08:16    [13121518]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Так я же написал выше, что запрос INSERT INTO [имя линкованного сервера].[имя базы].[dbo].[имя таблицы] SELECT * FROM [таблица локального сервера] выполняется.
Т.е. INSERT INTO обычного селекта выполняется нормально, а вот INSERT INTO селекта OPENROWSET (BULK...) не работает и выдает ошибку.
12 сен 12, 15:49    [13152611]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
Glory
Member

Откуда:
Сообщений: 104760
dzerkach
Т.е. INSERT INTO обычного селекта выполняется нормально, а вот INSERT INTO селекта OPENROWSET (BULK...) не работает и выдает ошибку.

А предварительный план выполнения у этого запроса какой ?
12 сен 12, 15:55    [13152670]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2995
dzerkach
Эх! Незадача! Уже и FORMATFILE научился делать и OPENROWSET (BULK...) уже заработал.
Но возникает ошибка INSERT INTO [таблица линкованного сервера] SELECT * FROM OPENROWSET (BULK...) -- не видит линкованный сервер.
В чем же дело?

видать такая реализация
действительно запрос
INSERT INTO [таблица линкованного сервера] SELECT * FROM OPENROWSET (BULK...) 

выдает ошибку
Invalid object name '[имя линкованного сервера].[имя базы].[dbo].[имя таблицы]'


но если сделать "финт ушами"

 INSERT INTO OPENROWSET(ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM  [таблица]') SELECT * FROM OPENROWSET (BULK...) 

то получим заветные
(N row(s) affected)


ЗЫЖ лучше заменить символ "*" на перечень полей
13 сен 12, 13:35    [13157513]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
HandKot
 INSERT INTO OPENROWSET(ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM  [таблица]') SELECT * FROM OPENROWSET (BULK...) 


HandKot, Спасибо! Все получилось. Только не OPENROWSET, а OPENQUERY
 INSERT INTO OPENQUERY (ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM  [таблица]') SELECT * FROM OPENROWSET (BULK...) 

И возник один негативный момент -- очень большое время выполнения запроса. Тесты проводил с файлом 500 тыс. строк. И вместо 10-15 сек. импорта на локальный сервер, с линкованным сервером вышло аж 20 мин. И в результате неожиданно пришла идея подойти к решению задачи совсем с другой стороны (односторонний взгляд никогда не помогал в решении проблем). В общем BULK INSERT ведь можно запускать на удаленном сервере, и загружать файл, расположенный на другом серваке, т.е. тянуть файл по сети. И надо было же зациклиться на запуске BULK INSERT на том же сервере, где лежит файл. Таким образом все работает почти так же быстро как и на локальном сервере.

Всем большое спасибо за советы и подсказки!
26 сен 12, 11:02    [13223609]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
Glory
Member

Откуда:
Сообщений: 104760
dzerkach
И возник один негативный момент -- очень большое время выполнения запроса. Тесты проводил с файлом 500 тыс. строк. И вместо 10-15 сек. импорта на локальный сервер, с линкованным сервером вышло аж 20 мин.

Потому что ваш запрос (ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM [таблица]') выкачивает всю таблицу с линкованного сервера
попробуйте (ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM [таблица] where 1=0')
26 сен 12, 16:18    [13226844]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать массовый импорт данных из файла в таблицу на другой sql server (linked server)  [new]
dzerkach
Member

Откуда:
Сообщений: 9
Glory
Потому что ваш запрос (ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM [таблица]') выкачивает всю таблицу с линкованного сервера попробуйте (ЛИНКОВАННЫЙ СЕРВЕР, 'SELECT * FROM [таблица] where 1=0')

Да дело в том, что таблица пустая и медленно происходит именно вставка записей. Я в процесе работы запроса делал SELECT из целевой таблицы и количество записей там увеличивалось очень медленно. Так что тормозит именно вставка записей. И оптимальный вариант, как я уже писал, это вызывать BULK INSERT на удаленном сервере, который импортирует файл лежащий на локальном сервере.
27 сен 12, 11:49    [13230644]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить