/******* Экспорт в Excel
exec master..xp_cmdshell 'bcp quietb.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Импорт Excel
выбирать *
из opendatasource('microsoft.jet.oledb.4.0',
'источник данных="c:test.xls";идентификатор пользователя=admin;пароль=;расширенные свойства=excel 5.0')...xactions
выберите cast(cast(номер учетной записи как числовой(10,2)) как nvarchar(255))+' ' преобразованный псевдоним
из opendatasource('microsoft.jet.oledb.4.0',
'источник данных="c:test.xls";идентификатор пользователя=admin;пароль=;расширенные свойства=excel 5.0')...xactions
/** Импортируем текстовый файл
exec master..xp_cmdshell 'bcp dbname..tablename в c:dt.txt -c -sservername -usa -ppassword'
/** Экспортируем текстовый файл
exec master..xp_cmdshell 'bcp "dbname..tablename" out c:dt.txt -c -sservername -usa -ppassword'
Это предложение необходимо заключить в кавычки
или
exec master..xp_cmdshell 'bcp "выбрать * из имени базы данных..имя_таблицы" queryout c:dt.txt -c -sservername -usa -ppassword'
Экспортировать в текст txt, разделенный запятыми.
exec master..xp_cmdshell 'bcp "Имя библиотеки..Имя таблицы" out "d:tt.txt" -c -t, -u sa -p пароль'
имя библиотеки массовой вставки..имя таблицы
из 'c:test.txt'
с (
терминатор поля = ';',
терминатор строки = 'n'
)
--/* файл dbase iv
выберите * из
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','select * from [Данные клиента 4.dbf]')
--*/
--/* файл dbase iii
выберите * из
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','select * from [Данные клиента 3.dbf]')
--*/
--/* база данных foxpro
выберите * из openrowset('msdasql',
'driver=драйвер Microsoft Visual FoxPro;sourcetype=dbf;sourcedb=c:',
'выбрать * из [aa.dbf]')
--*/
/******************Импорт файла dbf******************/
выберите * из openrowset('msdasql',
'driver = драйвер Microsoft Visual FoxPro;
sourcedb=e:vfp98data;
тип источника = dbf',
'выберите * у клиента, где страна != "США" заказ по стране')
идти
/************************ Экспорт в dbf ***************/
Если вы хотите экспортировать данные в сгенерированную структуру (т. е. существующую) таблицу foxpro, вы можете напрямую использовать следующий оператор sql.
вставить в openrowset('msdasql',
'driver=драйвер Microsoft Visual FoxPro;sourcetype=dbf;sourcedb=c:',
'выбрать * из [aa.dbf]')
выбрать * из таблицы
проиллюстрировать:
sourcedb=c: указывает папку, в которой находится таблица foxpro.
aa.dbf указывает имя файла таблицы foxpro.
/******************Экспорт для доступа************************/
вставьте в openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',таблица) выберите * из имени базы данных..b таблица
/******************доступ к импорту************************/
вставить в таблицу b select * from openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',таблица)
********************* Импортировать XML-файл.
объявить @idoc int
объявить @doc varchar(1000)
--пример XML-документа
установите @doc ='
<корень>
<клиент cid="c1" name="janine" city="issaquah">
<order oid="o1" date="20.01.1996" sum="3.5" />
<order oid="o2" date="30.04.1997" sum="13.4">клиент остался очень доволен
</заказ>
</клиент>
<клиент cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="14.07.1999" sum="100" note="оберните его синим цветом
белый красный">
<urgency>важно</urgency>
счастливый клиент.
</заказ>
<order oid="o4" date="20.01.1996" sum="10000"/>
</клиент>
</корень>
'
-- создать внутреннее представление XML-документа.
exec sp_xml_preparedocument @idoc вывод, @doc
-- выполнить оператор выбора, используя поставщика набора строк openxml.
выбирать *
из openxml (@idoc, '/root/customer/order', 1)
с (oid char(5),
плавающая сумма,
комментарий ntext 'text()')
exec sp_xml_removedocument @idoc
/************************Импортировать всю базу данных************************ ***** *******************/
Хранимая процедура, реализованная с использованием bcp
/*
Реализация хранимых процедур для импорта/экспорта данных.
В соответствии с различными параметрами вы можете импортировать/экспортировать весь пример вызова базы данных/одной таблицы:
--export пример вызова
----Экспорт одной таблицы
exec file2table 'zj','','','xzkh_sa..информация о регионе','c:zj.txt',1
----Экспортировать всю базу данных
exec file2table 'zj','','','xzkh_sa','c:docman',1
--Импортировать пример вызова
----Импорт одной таблицы
exec file2table 'zj','','','xzkh_sa..информация о регионе','c:zj.txt',0
----Импортируйте всю базу данных
exec file2table 'zj','','','xzkh_sa','c:docman',0
*/
если существует (выберите 1 из sysobjects, где name='file2table' и objectproperty(id,'isprocedure')=1)
удалить процедуру file2table
идти
создать процедуру file2table
@servername varchar(200) --имя сервера
,@username varchar(200) --Имя пользователя, если используется метод проверки nt, оно будет пустым''
,@пароль varchar(200) --Пароль
,@tbname varchar(500) --database.dbo.имя таблицы Если вы не укажете:.dbo.имя таблицы, будут экспортированы все пользовательские таблицы базы данных.
,@filename varchar(1000) --Путь импорта/экспорта/имя файла. Если параметр @tbname указывает на экспорт всей базы данных, то этот параметр является путем хранения файла, а имя файла автоматически использует имя таблицы.txt.
,@isout бит --1 — экспорт, 0 — импорт
как
объявить @sql varchar(8000)
if @tbname Like '%.%.%' --Если указано имя таблицы, одна таблица будет экспортирована напрямую
начинать
установите @sql='bcp '+@tbname
+случай, когда @isout=1 then ' out ' else ' in ' end
+' " '+@имя_файла+' " /w'
+' /s '+@имя_сервера
+случай, когда isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@пароль,'')
исполнительный мастер..xp_cmdshell @sql
конец
еще
начать --экспортировать всю базу данных, определить курсоры и удалить все пользовательские таблицы
объявить @m_tbname varchar(250)
если верно(@filename,1)<>'' set @filename=@filename+''
set @m_tbname='declare курсор #tb для выбора имени из '+@tbname+'..sysobjects , где xtype=''u'''
исполнитель(@m_tbname)
открыть #tb
получить следующее из #tb в @m_tbname
пока @@fetch_status=0
начинать
set @sql='bcp '+@tbname+'..'+@m_tbname
+случай, когда @isout=1 then ' out ' else ' in ' end
+' " '+@имя_файла+@m_tbname+'.txt " /w'
+' /s '+@имя_сервера
+случай, когда isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@пароль,'')
исполнительный мастер..xp_cmdshell @sql
получить следующее из #tb в @m_tbname
конец
закрыть #tb
освободить место #tb
конец
идти
/************************Excel в txt************************ ****** *******************/
Хотите использовать
выберите * в opendatasource(...) из opendatasource(...)
Реализация импорта содержимого файла Excel в текстовый файл.
Предположим, в Excel есть два столбца, первый столбец — это имя, а второй столбец — номер счета (16 цифр).
А номер банковского счета после экспорта в текстовый файл делится на две части, отделяются первые 8 цифр и последние 8 цифр.
Если вы хотите вставить его с помощью приведенного выше оператора, текстовый файл должен существовать и содержать одну строку: имя, номер банковского счета 1, номер банковского счета 2.
Затем вы можете использовать следующий оператор для вставки. Обратите внимание, что имя файла и каталог могут быть изменены в соответствии с вашей реальной ситуацией.
вставить в
opendatasource('microsoft.jet.oledb.4.0'
,'text;hdr=да;база данных=c:'
)...[аа#txt]
--,аа#txt)
--*/
выберите имя, номер банковского счета 1 = слева (номер банковского счета, 8), номер банковского счета 2 = справа (номер банковского счета, 8)
от
opendatasource('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=да;imex=2;база данных=c:a.xls'
--,лист1$)
)...[лист1$]
Если вы хотите напрямую вставлять и генерировать текстовые файлы, используйте bcp
объявить @sql varchar(8000),@tbname varchar(50)
--Сначала импортируйте содержимое таблицы Excel в глобальную временную таблицу.
выберите @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='выберите имя, номер банковского счета 1=слева (номер банковского счета, 8), номер банковского счета 2 = справа (номер банковского счета, 8)
в '+@tbname+' из
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=да;imex=2;database=c:a.xls''
)...[лист1$]'
исполнитель(@sql)
--Затем используйте bcp для экспорта из глобальной временной таблицы в текстовый файл
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(local)" /p"" /c'
исполнительный мастер..xp_cmdshell @sql
--Удалить временную таблицу
exec('удалить таблицу '+@tbname )
Хранимая процедура импорта и экспорта файлов в базу данных с помощью bcp:
/*--bcp-Импорт и экспорт двоичных файлов
Поддерживает импорт/экспорт изображений, текста, полей ntext.
изображение подходит для двоичных файлов; ntext подходит для текстовых файлов данных;
Примечание. При импорте все строки, соответствующие условиям, будут перезаписаны.
При экспорте все строки, соответствующие условиям, также будут экспортированы в указанный файл.
Эта хранимая процедура использует bcp только для реализации Zou Jian 2003.08-----------------*/
/*--пример вызова
--Экспорт данных
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--Экспорт данных
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
если существует (выберите * из dbo.sysobjects, где id = object_id(n'[dbo].[p_binaryio]') и objectproperty(id, n'isprocedure') = 1)
процедура удаления [dbo].[p_binaryio]
идти
создать процедуру p_binaryio
@servename varchar (30), -- имя сервера
@username varchar (30), --username
@password varchar (30), --password
@tbname varchar (500), --database..имя таблицы
@fdname varchar (30), --Имя поля
@fname varchar (1000), --directory + имя файла, использовать/перезаписать во время обработки: @filename+.bak
@tj varchar (1000)='', --условия обработки. Для импорта данных, если условие содержит @fdname, укажите префикс имени таблицы.
@isout bit=1 --1 экспорт ((по умолчанию), 0 импорт
как
объявить @fname_in varchar(1000) --bcp имя файла ответа обработки
,@fsize varchar(20) --Размер файла, подлежащего обработке.
,@m_tbname varchar(50) --имя временной таблицы
,@sql varchar(8000)
--Получить размер импортированного файла
если @isout=1
установите @fsize='0'
еще
начинать
создать таблицу #tb (необязательное имя varchar (20), размер int
, дата создания varchar(10), время создания varchar(20)
, дата последней операции записи varchar(10), время последней операции записи varchar(20)
,дата последнего доступа varchar(10), время последнего доступа varchar(20),характеристика int)
вставить в #tb
exec master..xp_getfiledetails @fname
выберите @fsize=sizefrom #tb
удалить таблицу #tb
если @fsize равен нулю
начинать
напечатать «Файл не найден»
возвращаться
конец
конец
--Создать файл ответов обработки данных
set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
установите @sql='select * в '+@m_tbname+' from(
выберите ноль в качестве типа
объединить все, выбрать 0 в качестве префикса
Объединив все, выберите «+@fsize+» в качестве длины
объединить все, выбрать ноль в качестве конца
объединить все, выбрать в качестве формата значение null
) а'
исполнитель(@sql)
выберите @fname_in=@fname+'_temp'
,@sql='bcp " '+@m_tbname+' " out " '+@fname_in
+'" /s" '+@servename
+случай, когда isnull(@username,'')='' then ''
else '" /u" '+@имя_пользователя конец
+'" /p"'+isnull(@password,'')+'" /c'
исполнительный мастер..xp_cmdshell @sql
--Удалить временную таблицу
set @sql='drop table '+@m_tbname
исполнитель(@sql)
если @isout=1
начинать
set @sql='bcp "выберите верхний 1 '+@fdname+' из '
+@tbname+case isnull(@tj,'') if '' then ''
else ' где '+@tj конец
+'" запрос " '+@fname
+'" /s" '+@servename
+случай, когда isnull(@username,'')='' then ''
else '" /u" '+@имя_пользователя конец
+'" /p"'+isnull(@пароль,'')
+'" /i" '+@fname_in+'"'
исполнительный мастер..xp_cmdshell @sql
конец
еще
начинать
--Подготовка временных таблиц для импорта данных
установите @sql='select top 0 '+@fdname+' в '
+@m_tbname+' из ' +@tbname
исполнитель(@sql)
--Импортировать данные во временную таблицу
установите @sql='bcp " '+@m_tbname+' " в " '+@fname
+'" /s" '+@servename
+случай, когда isnull(@username,'')='' then ''
else '" /u" '+@имя_пользователя конец
+'" /p"'+isnull(@пароль,'')
+'" /i" '+@fname_in+'"'
исполнительный мастер..xp_cmdshell @sql
--Импортировать данные в формальные таблицы
установите @sql='update '+@tbname
+' set '+@fdname+'=b.'+@fdname
+' из '+@tbname+' a,'
+@m_tbname+' б'
+case isnull(@tj,'') когда '' тогда ''
else ' где '+@tj конец
исполнитель(@sql)
--Удалить временную таблицу обработки данных
set @sql='drop table '+@m_tbname
конец
--Удалить файл ответов обработки данных
set @sql='del '+@fname_in
исполнительный мастер..xp_cmdshell @sql