存储过程

Transact-SQL 参考OBJECT_ID
返回数据库对象标识号。

语法
OBJECT_ID ( 'object' )

参数
'object'

要使用的对象。object 的数据类型为 char 或 nchar。如果 object 的数据类型是 char，那么隐性将其转换成 nchar。

返回类型
int

注释
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。

如果指定一个临时表名，则必须在临时表名前面加上数据库名，例如：

SELECT OBJECT_ID('tempdb..#mytemptable')

系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。有关更多信息，请参见表达式和 WHERE。

示例
下面的示例为 pubs 数据库中的 authors 表返回对象 ID。

USE master
SELECT OBJECT_ID('pubs..authors')

下面是结果集：

-----------
1977058079

(1 row(s) affected)

 

启用xp_cmdshell：
SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell'  的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

SQL   Server   2005   中引入的    xp_cmdshell   选项是服务器配置选项，使系统管理员能够控制是否可以在系统上执行   xp_cmdshell   扩展存储过程。默认情况下，xp_cmdshell   选项在新安装的软件上处于禁用状态，但是可以通过使用外围应用配置器工具或运行    sp_configure   系统存储过程来启用它，如下面的代码示例所示：
连上后,用查询分析器,依次执行下面的语句,就可以了.
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure  'show advanced options', 0
导出文本：
exec   master..xp_cmdshell   'bcp   _PY020_yayoi.dbo.T_Bumon   out   d:/tt.tx
t   -c  -S zhongchengli/yayoi    -U sa -P YAYOI'
/*******  导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:/temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

/***********  导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:/test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=远程ip;User ID=sa;Password=密码'
         ).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in 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 "Select * from dbname..tablename" queryout c:/DT.txt -c -Sservername -Usa -Ppassword'

导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:/tt.txt" -c -t ,-U sa -P password'

BULK INSERT 库名..表名
FROM 'c:/test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '/n'
)

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:/','select * from [客户资料4.dbf]')
--*/

--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:/','select * from [客户资料3.dbf]')
--*/

--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/',
'select * from [aa.DBF]')
--*/

/**************导入DBF文件****************/
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
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/',
'select * from [aa.DBF]')
select * from 表

说明:
SourceDB=c:/  指定foxpro表所在的文件夹
aa.DBF        指定foxpro表的文件名.

 

/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:/A.mdb';'admin';'',A表) select * from 数据库名..B表

/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
   'x:/A.mdb';'admin';'',A表)

文件名为参数
declare @fname varchar(20)
set @fname = 'd:/test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
    '''+@fname+''';''admin'';'''', topics) as a ')

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="f:/northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...产品

*********************  导入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
#NAME?
SET @doc ='
<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="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>
'
#NAME?
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

#NAME?
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
      WITH (oid     char(5),
            amount  float,
            comment ntext 'text()')
EXEC sp_xml_removedocument @idoc

 

???????

/**********************Excel导到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件

假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。

邹健：
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.

insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:/'
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls'
--,Sheet1$)
)...[Sheet1$]

 

如果你想直接插入并生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

#NAME?
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
 ,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
 into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls''
)...[Sheet1$]'
exec(@sql)

#NAME?
set @sql='bcp "'+@tbname+'" out "c:/aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql

#NAME?
exec('drop table '+@tbname)

/********************导整个数据库*********************************************/

用bcp实现的存储过程

/*
 实现数据导入/导出的存储过程
         根据不同的参数,可以实现导入/导出整个数据库/单个表
 调用示例:
#NAME?
#NAME?
exec file2table 'zj','','','xzkh_sa..地区资料','c:/zj.txt',1
#NAME?
exec file2table 'zj','','','xzkh_sa','C:/docman',1

#NAME?
#NAME?
exec file2table 'zj','','','xzkh_sa..地区资料','c:/zj.txt',0
#NAME?
exec file2table 'zj','','','xzkh_sa','C:/docman',0

*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
 drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)  --服务器名
,@username varchar(200)   --用户名,如果用NT验证方式,则为空''
,@password varchar(200)   --密码
,@tbname varchar(500)   --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000)  --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit      --1为导出,0为导入
as
declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
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_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
 declare @m_tbname varchar(250)
 if right(@filename,1)<>'/' set @filename=@filename+'/'

 set @m_tbname='declare #tb 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(@password,'')
  exec master..xp_cmdshell @sql
  fetch next from #tb into @m_tbname
 end
 close #tb
 deallocate #tb
end
go

/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO

delete from openquery(mailser,'select *  from yulin')

select *  from openquery(mailser,'select *  from yulin')

update openquery(mailser,'select * from  yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from  yulin')values(333,777)

 

补充：

对于用bcp导出,是没有字段名的.

用openrowset导出,需要事先建好表.

用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

 

 

sp_executesql
执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。

语法
sp_executesql [@stmt=] stmt
[
    {, [@params=] N'@parameter_name  data_type [,...n]' }
    {, [@param1 =] 'value1' [,...n] }
]

参数
[@stmt=] stmt

包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。

stmt 可以包含与变量名形式相同的参数，例如：

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。

[@params=] N'@parameter_name  data_type [,...n]'

字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。

[@param1 =] 'value1'

参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。

n

附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。

返回代码值
0（成功）或 1（失败）

结果集
从生成 SQL 字符串的所有 SQL 语句返回结果集。

注释
在批处理、名称作用域和数据库上下文方面，sp_executesql 与 EXECUTE 的行为相同。sp_executesqlstmt 参数中的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。然后编译 stmt 中的内容并作为执行计划运行（独立于名为 sp_executesql 的批处理的执行计划）。sp_executesql 批处理不能引用调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标和变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所作的更改只在 sp_executesql 语句结束前有效。

如果只更改了语句中的参数值，则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变仅参数值变化，所以 Microsoft® SQL Server™ 查询优化器可能重复使用首次执行时所生成的执行计划。

说明  如果语句字符串中的对象名不是全限定名，则该执行计划不会被重用。

sp_executesql 支持与 Transact-SQL 字符串相独立的参数值的设置：

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

替换 sp_executesql 中的参数的能力，与使用 EXECUTE 语句执行字符串相比，有下列优点：

因为在 sp_executesql 中，Transact-SQL 语句的实际文本在两次执行之间未改变，所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。这样，SQL Server 不必编译第二条语句。

Transact-SQL 字符串只生成一次。

整型参数按其本身格式指定。不需要转换为 Unicode。
权限
执行权限默认授予 public 角色。

示例
A. 执行简单的 SELECT 语句
下面的示例创建并执行一个简单的 SELECT 语句，其中包含名为 @level 的嵌入参数。

execute sp_executesql
          N'select * from pubs.dbo.employee where job_lvl = @level',
          N'@level tinyint',
          @level = 35

B. 执行动态生成的字符串
下面的示例显示使用 sp_executesql 执行动态生成的字符串。该示例中的存储过程用来向一组表中插入数据，该表用于划分一年的销售数据。一年中的每个月均有一个表，格式如下：

CREATE TABLE May1998Sales
    (OrderID      INT      PRIMARY KEY,
    CustomerID      INT      NOT NULL,
    OrderDate      DATETIME   NULL
        CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth      INT
        CHECK (OrderMonth = 5),
    DeliveryDate   DATETIME   NULL,
        CHECK (DATEPART(mm, OrderDate) = OrderMonth)
    )

有关从这些分区表中检索数据的更多信息，请参见使用包含分区数据的视图。

每个表的名称由月份名的前三个字母、年度的四位数字和常量 Sales 组成。名称可以从订单日期动态生成：

/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'

下面示例中的存储过程动态生成并执行一个 INSERT 语句，向适当的表中插入新订单。该存储过程使用订单日期生成应包含数据的表的名称，然后将名称并入 INSERT 语句。（这是 sp_executesql 的一个简单示例。不包含错误检查，也不包括业务规则检查，例如确保两个表之间订单号没有重复。）

CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

#NAME?
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
     @OrderMonth, @PrmDeliveryDate

GO

在该过程中使用 sp_executesql 比使用 EXECUTE 执行字符串更有效。使用 sp_executesql 时，只生成 12 个版本的 INSERT 字符串，每个月的表 1 个。使用 EXECUTE 时，因为参数值不同，每个 INSERT 字符串均是唯一的。尽管两种方法生成的批处理数相同，但因为 sp_executesql 生成的 INSERT 字符串相似，所以查询优化程序更有可能反复使用执行计划。

 

 

取硬盘的序列号可以自己写外部存储过程生成dll解决，数据盘上有的处理c盘的数据如文件，以前见过，没试过，帮你找出来如下：

 

如，在存储过程中对一个文本文件c:/ttt.txt;
怎么实现从文本中读取一行，怎么实现写入一行呢？

答：
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
Declare @tmp int
declare @msg varchar(3000)

SET @msg='Hello. MS SQL Server 2000. I Love you!!!'   --這字串將會被寫到SQL Server的安裝目錄下的Nipsan.Txt文件里面

/*  取得SQL Server的安裝路徑*/
declare @strPath  nvarchar(512)
Exec sp_MSGet_Setup_paths @strPath OUTPUT
Set @strPath=@strPath+'/Nipsan.Txt'   --組成文件名
 

#NAME?
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

#NAME?
EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @tmp OUTPUT , @strPath
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END
--把@msg寫到文件里面去
EXEC @hr = sp_OAMethod @tmp, 'Write',NULL, @msg
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END
#NAME?
EXEC @hr = sp_OAMethod @tmp, 'Close',NULL
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END
-----------寫文件操作Demo完成---------------------------------------------------------------

#NAME?
EXEC @hr = sp_OAMethod @object, 'OpenTextFile', @tmp OUTPUT ,@strPath
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END

SET @msg=''
#NAME?
EXEC @hr = sp_OAMethod @tmp, 'Read', @msg OUT,3000
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END
#NAME?

SELECT @msg AS RESULT

 

 

没有这么用过，不过可以提供几个方向的思路，从理论上说，SQL SERVER 能访问所有格式化的数据，要求文本文件有一定格式。
1、通过Microsoft.Jet.OLEDB.4.0连接成一个SERVER，再象访问一般连接数据库一样用SQL语句访问。以下一段从SQL SERVER帮助拷贝，是说明这个用法的。
2、用1法建立的连接SERVER，也可以通过OPENQUERY访问，具体可以看SQL SERVER的帮助。
3、非格式化的一些数据可以用xp_cmdshell调用DOS命令来写入文本文件，如这个小例子，写入一个变量到文本文件这段代码可以修改后嵌到你的存储过程里：
DECLARE @var varchar(100)
DECLARE @i int
DECLARE @cmd sysname
set @i=120
SET @var = '@i='+cast(@i as varchar(10))
SET @cmd = 'echo ' + @var + ' > VarOut.txt'
EXEC master..xp_cmdshell @cmd
4、高手门可能还有好方法。

 

--------------以下是从SQL SERVER帮助拷贝的帮助------------------

H. 使用用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件
此示例创建一台直接访问文本文件的链接服务器，而没有将这些文件链接为 Access .mdb 文件中的表。提供程序是 Microsoft.Jet.OLEDB.4.0，提供程序字符串为"Text"。

数据源是包含文本文件的目录的完整路径名。schema.ini 文件（描述文本文件的结构）必须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息，请参见 Jet 数据库引擎文档。

#NAME?
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:/data/distqry',
   NULL,
   'Text'
GO

#NAME?
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

#NAME?
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。授予非管理用户执行 xp_cmdshell 的权限。

说明  在 Microsoft® Windows® 95 或 Microsoft Windows 98 操作系统中执行 xp_cmdshell 时，将不把 xp_cmdshell 的返回代码设置为唤醒调用的可执行文件的进程退出代码。返回代码始终为 0。

语法
xp_cmdshell {'command_string'} [, no_output]

参数
'command_string'

是在操作系统命令行解释器上执行的命令字符串。command_string 的数据类型为 varchar(255) 或 nvarchar(4000)，没有默认值。command_string 不能包含一对以上的双引号。如果由 command_string 引用的文件路径或程序名称中有空格，则需要使用一对引号。如果使用嵌入空格不方便，可考虑使用 FAT 8.3 文件名作为解决办法。

no_output

是可选参数，表示执行给定的 command_string，但不向客户端返回任何输出。

返回代码值
0（成功）或 1（失败）

结果集
执行下列 xp_cmdshell 语句将返回当前目录的目录列表。

xp_cmdshell 'dir *.exe'

行以 nvarchar(255) 列的形式返回。

执行下列 xp_cmdshell 语句将返回随后的结果集：

xp_cmdshell 'dir *.exe', NO_OUTPUT

下面是结果：

The command(s) completed successfully.

注释
xp_cmdshell 以同步方式操作。在命令行解释器命令执行完毕之前，不会返回控制。

当授予用户执行权限时，用户能在 Microsoft Windows NT® 命令行解释器上执行运行 Microsoft SQL Server™ 的帐户有权执行的任何操作系统命令。

默认情况下，只有 sysadmin 固定服务器角色的成员才能执行此扩展存储过程。但是，也可以授予其他用户执行此存储过程的权限。

当作为 sysadmin 固定服务器角色成员的用户唤醒调用 xp_cmdshell 时，将在运行 SQL Server 服务的安全上下文中执行 xp_cmdshell。当用户不是 sysadmin 组的成员时，xp_cmdshell 将模拟使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帐户。如果代理帐户不能用，则 xp_cmdshell 将失败。这只是针对于 Microsoft® Windows NT® 4.0 和 Windows 2000。在 Windows 9.x 上，没有模拟，且 xp_cmdshell 始终在启动 SQL Server 的 Windows 9.x 用户的安全上下文下执行。

说明  在早期版本中，获得 xp_cmdshell 执行权限的用户在 MSSQLServer 服务的用户帐户上下文中运行命令。可以通过配置选项配置 SQL Server，以便对 SQL Server 无 sa 访问权限的用户能够在 SQLExecutiveCmdExec Windows NT 帐户的上下文中运行 xp_cmdshell。在 SQL Server 7.0 中，该帐户称为 SQLAgentCmdExec。现在，不是 sysadmin 固定服务器角色成员的用户将在该帐户上下文中运行命令，而无需再进行配置更改。

权限
xp_deletemail 的执行权限默认授予 sysadmin 固定服务器角色的成员，但可以授予其他用户。

重要  如果为 MSSQLServer 服务选用的 Windows NT 帐户不是本地管理员组的成员，则非 sysadmin 固定服务器角色成员的用户将无法执行 xp_cmdshell。

示例
A. 返回可执行文件列表
下例显示执行目录命令的 xp_cmdshell 扩展存储过程。

EXEC master..xp_cmdshell 'dir *.exe'

B. 使用 Windows NT net 命令
下例显示 xp_cmdshell 在存储过程中的使用。下例先用 net send 通知用户 SQL Server 即将关闭，然后用 net pause 暂停服务器，最后用 net stop 关闭服务器。

CREATE PROC shutdown10
AS
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
   in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down
   in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
   in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output

C. 不返回输出
下例使用 xp_cmdshell 执行命令字符串，且不向客户端返回输出。

USE master
EXEC xp_cmdshell 'copy c:/sqldumps/pubs.dmp //server2/backups/sqldumps',
   NO_OUTPUT

D. 使用返回状态
在下例中，xp_cmdshell 扩展存储过程也给出了返回状态。返回代码值存储在变量 @result 中。

DECLARE @result int
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
   PRINT 'Success'
ELSE
   PRINT 'Failure'

E. 将变量内容写入文件
下例将当前目录内容写入当前服务器目录下名为 dir_out.txt 的文件中。

DECLARE @cmd sysname, @var sysname
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

 

 

 

CREATE procedure dbo.Pro_GetdudoRireki @tableName varchar(100) ,@tableId int,@oldGetsudoId int,@newGetsudoId int
AS SET NOCOUNT  ON   
if(@newGetsudoId <> @oldGetsudoId)   
 begin  
 declare @minId int, @maxId int, @nextId int, @range int, @intErrorCode int,@intRowcount int, @sql_select nvarchar(400), @sql_insert varchar(400) 
 , @sql_tempTableName varchar(100) , @sql_tempTableCount int 
 set @sql_tempTableName  = '##tem_GetsudoRireki' 
 set @sql_tempTableCount = 0 
 WHILE object_id('tempdb..'+@sql_tempTableName)   is   not   null 
 begin 
 set @sql_tempTableCount =@sql_tempTableCount+1 
 set @sql_tempTableName = '##tem_GetsudoRireki'+STR(@sql_tempTableCount,@sql_tempTableCount/10+1) 
 end 
 set @sql_select = 'select * into '+ @sql_tempTableName   +' from ' + @tableName   + ' where KyuyoGetsudoId = ' + STR(@oldGetsudoId) 
 exec(@sql_select) set @intRowcount = @@rowcount select @intErrorCode = @@ERROR   
 if @intRowcount <> 0  
  begin  
  set @sql_select  = N'select  @minId = min('+@sql_tempTableName+'.Id)  from '+ @sql_tempTableName
  exec sp_executesql @sql_select,N'@minId int output',@minId output
  set @sql_select  = N'select  @maxId = max('+@sql_tempTableName+'.Id)  from '+ @sql_tempTableName
  exec sp_executesql @sql_select,N'@maxId int output',@maxId output
  exec @nextId =   pro_GetSerialNextId @tableId
  set @range  = (@nextId - @minId + @maxId + 1)
  exec pro_SetSerialNextId @tableId, @range
  set @sql_select  = 'update ' + @sql_tempTableName +' set Id   =  '+ @sql_tempTableName+'.Id +'+STR(@nextId)+'-'+STR(@minId)+
  ',KyuyoGetsudoId  = '+ STR(@newGetsudoId)
  exec(@sql_select)
  set @sql_insert = 'insert  into ' + @tableName +  ' select *  from  '+ @sql_tempTableName
  exec(@sql_insert)
 end 
 set @sql_select  = 'drop   table '+ @sql_tempTableName 
 exec(@sql_select)
 return @@ERROR
 end 

 

sqlserver海量数据导入的最快方法 

ie  相关内容：ie 
　　最近做某项目的数据库分析，要实现对海量数据的导入问题，就是最多把200万条数据一次导入sqlserver中，如果使用普通的insert语句进行写出的话，恐怕没个把小时完不成任务，先是考虑使用bcp，但这是基于命令行的，对用户来说友好性太差，实际不大可能使用；最后决定使用BULKINSERT语句实现，BULKINSERT也可以实现大数据量的导入，而且可以通过编程实现，界面可以做的非常友好，它的速度也很高：导入100万条数据不到20秒中，在速度上恐怕无出其右者。 
但是使用这种方式也有它的几个缺点： 
1.需要独占接受数据的表 
2.会产生大量的日志 
3.从中取数据的文件有格式限制 
但相对于它的速度来说，这些缺点都是可以克服的，而且你如果愿意牺牲一点速度的话，还可以做更精确的控制，甚至可以控制每一行的插入。 
对与产生占用大量空间的日志的情况，我们可以采取在导入前动态更改数据库的日志方式为大容量日志记录恢复模式，这样就不会记录日志了，导入结束后再恢复原来的数据库日志记录方式。 
具体的一个语句我们可以这样写：
alterdatabasetaxi
setRECOVERYBULK_LOGGEDBULKINSERTtaxi..detailFROMe:out.txt
WITH(
??DATAFILETYPE=char,
??FIELDTERMINATOR=,,
??ROWTERMINATOR= ,
?TABLOCK
)alterdatabasetaxi
setRECOVERYFULL这个语句将从e:out.txt导出数据文件到数据库taxi的detail表中。

-----Original Message-----
From: zhongchengli [mailto:[e-mail]]
Sent: Thursday, January 04, 2007 1:45 PM
To: 'hanjie'
Cc: 'yangleigang'; '[e-mail]'; 'Dai Xiaoguang'
Subject: RE: insert

mysql 数据库insert优化方法：
插入一个记录的时间由下列组成：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1 x 记录大小）
插入索引：（1 x 索引）
关闭：(1)

这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。
表的大小以N log N (B 树)的速度减慢索引的插入。

加快插入的一些方法：
如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。见7.14 INSERT句法。

注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。

当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。见7.16 LOAD DATA INFILE句法。
当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：

有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。
执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。

用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。
如果你有myisampack并且想要压缩表，在它上面运行myisampack。见10.6.3 压缩表的特征。

用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。

执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。
这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。

你可以锁定你的表以加速插入。
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES
mysql> (8,26),(6,29); UNLOCK TABLES;

主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

如果你不使用锁定，2、3和4将在1和5前完成。如果你使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。因为INSERT, UPDATE和DELETE操作在MySQL中是很快的，通过为多于大约5次连续不断地插入或更新一行的东西加锁，你将获得更好的整体性能。

如果你做很多一行的插入，你可以做一个LOCK TABLES，偶尔随后做一个UNLOCK TABLES(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，LOAD DATA INFILE对装载数据仍然是更快的。

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

在插入记录较多的时候，省时又少错（执行一个语句比执行多个语句来得更可靠，根本就不用考虑事务来约束了），妙！

insert into aa(a,b,c) values(1,2,3)

insert into aa(a,b,c)
select 3,4,5
union all
select 6,7,8

insert into bb(a,b,c) select a,b,c from aa
 
insert into bb(a,b,c)
select a,b,c from aa where a=1
union all
select a,b,c from aa where a=2

上边代码中的into都可以省略！

上边代码中的union all如果换成union，则相同记录只插入一次，不会重复插入。
Yours sincerely,
ChengLi Zhong

                         Edge Software Development(Dalian) Co.,ltd
                         Room 106,A Bldg.1 Huoju Road
                         Hi-Tech Zone,Dalian,China
                         Zip:116023
                         Tel:[phone]
                         Fax:[phone]
                         http://www.livedoor.cn
                         E-mail:[e-mail]

 

存储过程中的循环语句
                                      

WHILE
设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。

语法
WHILE Boolean_expression
    { sql_statement | statement_block }
    [ BREAK ]
    { sql_statement | statement_block }
    [ CONTINUE ]

参数
Boolean_expression

返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。

{sql_statement | statement_block}

Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块，请使用控制流关键字 BEGIN 和 END。

BREAK

导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。

CONTINUE

使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后的任何语句。

注释
如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。

示例
A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE
在下例中，如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

B. 在带有游标的过程中使用 WHILE
以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中，该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0，所以，所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除，将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。

USE pubs
DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

 

 if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[mama]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [dbo].[mama]

if   object_id('tempdb..##tmp1')   is   not   null    
  drop   table   ##tmp1