(Ms SQL Server) SQL statement Import and Export Daquan

Ms SQL Server) SQL statement Import and Export Daquan [turn] [] title (ms SQL Server) SQL statement Import and Export Daquan choose from lchzh Blog keywords (ms SQL Server) SQL statement Import and Export Daquan source http://dev.csdn.net/develop/article/61/61087.shtm SQL statement Import and Export Daquan/***** export to excel EXEC master .. xp_mongoshell 'bcp SettleDB. dbo. shanghu out c: \ temp1.xls-c-q-S "GNETDATA/GNETDATA"-U "sa"-P "'/*********** import Excel SELECT * FROM openDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "C: \ test.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactions SELECT cast (subject number as numeric (255) as nvarchar () + ''converted alias FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ test.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactions/** import text file EXEC master .. xp_mongoshell 'bcp "dbname .. tablename "in c: \ DT.txt-c-Sservername-Usa-ppassword '/ ** Export the text file EXEC master .. xp_mongoshell 'bcp "dbname .. tablename "out c: \ DT.txt-c-Sservername-Usa-ppassword' or EXEC master .. xp_mongoshell 'bcp "Select * from dbname .. tablename "queryout c: \ DT.txt-c-Sservername-Usa-ppassword' export to TXT text and use commas to separate exec master .. xp_mongoshell 'bcp "database name .. table Name "out" d: \ tt.txt "-c-t,-U sa-P password 'bulk INSERT database name .. table name FROM 'C: \ test.txt 'WITH (FIELDTERMINATOR ='; ', ROWTERMINAT OR = '\ n') --/* dBase IV file select * from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase IV; HDR = NO; IMEX = 2; DATABASE = C: \', 'select * from [customer profile 4.dbf] ') -- */--/* select * from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; HDR = NO; IMEX = 2; DATABASE = C: \', 'select * from [Customer Information 3.dbf] ') -- */--/* select * from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c: \ ', 'select * from [aa. DBF] ') -- * // ************** import the DBF File ******************/select * from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceDB = e: \ VFP98 \ data; SourceType = dbf', 'select * from customer where country! = "USA" order by country ') go/***************** export to DBF *****************/if you want export data to a generated structure (that is, existing) in the FOXPRO table, you can directly use the following SQL statement insert into openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :\', 'select * from [aa. DBF] ') select * from table description: SourceDB = c: \ specifies the folder aa where the foxpro table is located. DBF specifies the name of The foxpro table. /* openrowset ('Microsoft. jet. OL EDB.4.0 ', 'x: \. mdb '; 'admin'; '', table A) select * from database name .. table B/**********************************/insert into B Table selet * from openrowset ('Microsoft. jet. OLEDB.4.0 ', 'x: \. mdb '; 'admin'; '', Table) * ******************* import the xml file DECLARE @ idoc int DECLARE @ doc varchar (1000) -- sample XML document SET @ doc = '<root> <Customer cid = "C1" name = "Janine" city = "Issaquah"> <Order oid = "O1" date =" 1/20/1996 "amo Unt = "3.5"/> <Order oid = "O2" date = "4/30/1997" amount = "13.4"> Customer was very satisfied </Order> </Customer> <Customer cid = "C2" name = "Ursula" city = "Oelde"> <Order oid = "O3" date = "7/14/1999" amount = "100" note = "Wrap it blue white red "> <Urgency> Important </Urgency> Happy Customer. </Order> <Order oid = "O4" date = "1/20/1996" amount = "10000"/> </Customer> </root> '-- Create an internal representation The XML document. EXEC sp_xml_preparedocument @ idoc OUTPUT, @ doc -- Execute a SELECT statement using OPENXML rowset provider. SELECT * from openxml (@ idoc, '/root/Customer/order', 1) WITH (oid char (5), amount float, comment ntext 'text ()') EXEC sp_xml_removedocument @ idoc ************ * *******************************/stored procedures implemented using bcp /* The stored procedure for data import/export varies with parameters, you can import/export the entire database/ Single Table call example: -- export call example ---- export a single table exec file2table 'zj', '','', 'xzkh _ sa .. region information ', 'c: \ zj.txt', 1 ---- export the entire database exec file2table 'zj', '','', 'xzkh _ Sa', 'c: \ docman ', 1 -- import call example ---- import a single table exec file2table 'zj', '','', 'xzkh _ sa .. region information ', 'c: \ zj.txt', 0 ---- import the entire database exec file2table 'zj', '','', 'xzkh _ Sa', 'c: \ docman ', 0 */if exists (select 1 from sysobjects where name = 'file2table' and objectproperty (id, 'isprocedure') = 1) drop proce Dure File2Table go create procedure File2Table @ servername varchar (200) -- server name, @ username varchar (200) -- user name. If you use the NT authentication method, it is null '', @ password varchar (200) -- password, @ tbname varchar (500) -- database. dbo. table name, if not specified :. dbo. the table name is used to export all the user tables of the database. @ filename varchar (1000) -- import/export path/export, @ isout bit -- 1 is the export, 0 is imported as declare @ SQL varchar (8000) if @ tbname like '%. %. % '-- if the table name is specified, a single table is exported directly. Begin set @ SQL = 'bcp' + @ tbname + case when @ isout = 1 then 'out' else' in 'end + '"' + @ filename + '"/W' + '/s' + @ servername + case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end + '/P' + isnull (@ password,'') exec master .. xp_mongoshell @ SQL end else begin -- export the entire database, define the cursor, and retrieve all user tables declare @ m_tbname varchar (250) if right (@ filename, 1) <> '\' set @ filename = @ filename + '\' set @ m_tbname = 'Clare # t B cursor for select name from '+ @ tbname + '.. sysobjects where xtype = ''u''' exec (@ m_tbname) open # tb fetch next from # tb into @ m_tbname while @ fetch_status = 0 begin set @ SQL = 'bcp' + @ tbname + '.. '+ @ m_tbname + case when @ isout = 1 then 'out' else' in 'end +' "'{@filename}@m_tbname}'.txt"/W' + '/s' + @ servername + case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end + '/P' + isnull (@ passwor D, '') exec master .. xp_shortshell @ SQL fetch next from # tb into @ m_tbname end close # tb deallocate # tb end go /******************* * ** export to Txt through Excel ********************************* * ******/select * into opendatasource (...) from opendatasource (...) import the content of an Excel file to a text file. Assume that there are two columns in Excel, the first column is name, and the second column is a very row account (16 digits) the exported bank accounts are divided into two parts: the first eight digits and the last eight digits. If you want to insert the statement above, the text file must exist and have one line: name, bank account 1, the bank account 2 can then be inserted with the following statement. Note that the file name and directory are modified according to your actual situation. insert into opendatasource ('Microsoft. JET. OLEDB.4.0 ', 'text; HDR = Yes; DATABASE = C :\')... [aa # txt] --, aa # txt) -- */select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8) from opendatasource ('Microsoft. JET. OLEDB.4.0 ', 'excel 5.0; HDR = YES; IMEX = 2; DATABASE = c: \ a.xls' --, Sheet1 $ ))... [Sheet1 $] to directly insert and generate a text file, use bcp declare @ SQL var Char (8000), @ tbname varchar (50) -- first import the excel table content to a global temporary table select @ tbname = '[# temp' + cast (newid () as varchar (40) + ']', @ SQL = 'select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8) into '+ @ tbname +' from opendatasource (''MICROSOFT. JET. OLEDB.4.0 '', ''excel 5.0; HDR = YES; IMEX = 2; DATABASE = c: \ a.xls '')... [Sheet1 $] 'exec (@ SQL) -- use bcp to export the global temporary table to the text file set @ SQL = 'bcp "'+ @ tbname +'" out "c: \ aa.txt "/S" (local) "/P" "/C' exec Master .. xp_mongoshell @ SQL -- delete the stored procedure of temporary table exec ('drop table' + @ tbname) using bcp to import and export files to the database: /* -- bcp-Import and Export of binary files supports image, text, and ntext fields. image is suitable for binary files. text and ntext are suitable for text data files. Note: during import, when all rows that meet the conditions are overwritten for export, all rows meeting the conditions will also be exported to the specified file. This stored procedure only uses bcp to build 2003.08 --------------- * // * -- call example -- data export exec p_binaryIO 'zj ', '','', 'Acc _ demo data .. tb', 'img ', 'c: \ zj1.dat' -- data export exec p_binaryIO 'zj', '','', 'Acc _ demo data .. tb', 'img ', 'c: \ zj1.dat', '', 0 -- */If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_binaryIO] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1) drop procedure [dbo]. [p_binaryIO] GO Create proc p_binaryIO @ servename varchar (30), -- server name @ username varchar (30), -- username @ password varchar (30 ), -- password @ tbname varchar (500), -- database .. table Name @ fdname varchar (30), -- field name @ fname varchar (1000), -- directory + file name, use/overwrite during processing: @ filename +. ba K @ tj varchar (1000) = '', -- processing condition. for data import, if the condition contains @ fdname, please specify the table name prefix @ isout bit = 1 -- 1 Export (default), 0 import AS declare @ fname_in varchar (1000) -- bcp processes the response file name, @ fsize varchar (20) -- size of the file to be processed, @ m_tbname varchar (50) -- temporary table name, @ SQL varchar (8000) -- Obtain the size of the import file if @ isout = 1 set @ fsize = '0' else begin create table # tb (optional name: varchar (20), size: int, creation date: varchar (10), Creation Time: varchar (20), last write operation date: varchar (10), last write operation time: varchar (20), last access date: varch Ar (10), last access time varchar (20), feature int) insert into # tb exec master .. xp_getfiledetails @ fname select @ fsize = size from # tb drop table # tb if @ fsize is null begin print 'file not found 'Return end -- generate data processing response file set @ m_tbname =' [## temp '+ cast (newid () as varchar (40 )) + '] 'set @ SQL = 'select * into' + @ m_tbname + 'from (select null as type union all select 0 as prefix union all select' + @ fsize +' length union all select null as ended Union all select null as format) a' exec (@ SQL) select @ fname_in = @ fname + '_ temp ', @ SQL = 'bcp "'+ @ m_tbname +'" out "'+ @ fname_in +'"/S "'+ @ servename + case when isnull (@ username ,'') = ''then'' else' "/U" '+ @ username end +' "/P" '+ isnull (@ password ,'') + '"/C' exec master .. xp_cmdshell @ SQL -- delete a temporary table set @ SQL = 'drop table' + @ m_tbname exec (@ SQL) if @ isout = 1 begin set @ SQL = 'bcp "select top 1' + @ fdname + 'from' + @ tbn Ame + case isnull (@ tj ,'') when ''then'' else 'where' + @ tj end + '"queryout"' + @ fname + '"/S"' + @ servename + case when isnull (@ username, '') = ''then'' 'else'"/U "'+ @ username end +'"/P "'+ isnull (@ password ,'') + '"/I"' + @ fname_in + '"'exec master .. xp_cmdshell @ SQL end else begin -- prepare a temporary table for data import. set @ SQL = 'select top 0' + @ fdname + 'in' + @ m_tbname + 'from' + @ tbname exec (@ SQL) -- import data to the temporary table set @ SQL = 'bcp "'+ @ m _ Tbname + '"in"' + @ fname + '"/S"' + @ servename + case when isnull (@ username ,'') = ''then'' else' "/U" '+ @ username end +' "/P" '+ isnull (@ password ,'') + '"/I"' + @ fname_in + '"'exec master .. xp_mongoshell @ SQL -- import data to the official table. set @ SQL = 'update' + @ tbname + 'set' + @ fdname + '= B. '+ @ fdname + 'from' + @ tbname + 'A,' + @ m_tbname + 'B' + case isnull (@ tj ,'') when ''then'' else 'where' + @ tj end exec (@ SQL) -- delete a temporary data processing table set @ SQL = 'Drop table' + @ m_tbname end -- delete the data processing response file set @ SQL = 'del '+ @ fname_in exec master .. xp_mongoshell @ SQL go/** import text file EXEC master .. xp_mongoshell 'bcp "dbname .. tablename "in c: \ DT.txt-c-Sservername-Usa-ppassword' is changed to the following, and EXEC master is not required in quotation marks .. xp_mongoshell 'bcp dbname .. tablename in c: \ DT.txt-c-Sservername-Usa-ppassword'/** export the text file EXEC master .. xp_mongoshell 'bcp "dbname .. tablename "out c: \ DT.txt-c-Sservernam E-Usa-ppassword' must be enclosed By [zbkid] in [code library] at 21:15:02 | Comments [0] | 37 views hides the file path using Asp, achieve anti-leech [] Source: http://siyizhu.com/blogview.asp? LogID = 330 if we know the actual path of a static file, for example, success! How can I prevent the downloader from obtaining the actual path when downloading 51windows.pdf on the website! This article describes how to use Asp to hide the actual download path of an object. When we manage website files, we can place files with the same extension in the same directory and create a special name. For example, we can save the following code as down. asp. Its online path is http://www.xx.com/down.asp. we can use http://www.xx.com/down.asp? Filenameapps51windows.pdf to download this file, and the downloader cannot see the actual download path of this file! In down. asp, you can also set whether to log on to the downloaded file to determine whether the downloaded Source Page is an external website, so as to prevent the file from being leeched. Sample Code: <% From_url = Cstr (Request. serverVariables ("HTTP_REFERER") Serv_url = Cstr (Request. serverVariables ("SERVER_NAME") if mid (From_url, 8, len (Serv_url) <> Serv_url then response. write "illegal link! "'Preventing leeching response. end if Request. Cookies (" Logined ") =" "then response. redirect"/login. asp "'requires login! End if Function GetFileName (longname) '/folder1/folder2/file. asp => file. asp while instr (longname, "/") longname = right (longname, len (longname)-1) wend GetFileName = longname End Function Dim Stream Dim Contents Dim FileName Dim TrueFileName Dim FileExt Const adTypeBinary = 1 FileName = Request. queryString ("FileName") if FileName = "" Then Response. write "invalid file name! "Response. end if FileExt = Mid (FileName, limit Rev (FileName ,". ") + 1) select Case UCase (FileExt) Case" ASP "," ASA "," ASPX "," ASAX "," MDB "Response. write "invalid operation! "Response. end select Response. clear if lcase (right (FileName, 3) = "gif" or lcase (right (FileName, 3) = "jpg" or lcase (right (FileName, 3 )) = "png" then Response. contentType = "image/*" 'does not display the Download Dialog Box else Response. contentType = "application/ms-download" end if Response. addHeader "content-disposition", "attachment; filename =" & GetFileName (Request. queryString ("FileName") Set Stream = server. createObject ("ADODB. stream ") Stream. type = adTypeBinary Stream. open if lcase (right (FileName, 3) = "pdf" then' set TrueFileName = "/the_1__file_s/" & FileName end if lcase (right (FileName, 3) = "doc" then' set the file directory of the DOC type TrueFileName = "/my_D_O_C_file/" & FileName end if lcase (right (FileName, 3 )) = "gif" or lcase (right (FileName, 3) = "jpg" or lcase (right (FileName, 3 )) = "png" then TrueFileName = "/all_images _/" & FileName 'sets the image file directory end if Stream. loadFromFile Server. mapPath (TrueFileName) While Not Stream. EOS Response. binaryWrite Stream. read (1024*64) Wend Stream. close Set Stream = Nothing Response. flush Response. end %>