- 博客(0)
- 资源 (7)
- 收藏
- 关注
修改表结构的 asp C# 源代码
var sNewFieldString = '<s'+'cript language="JavaScript">'
+ '\n' + getObjByID.toString()
+ '\n' + showFieldType.toString()
+ '\n' + checkFieldType.toString()
+ '\n' + checkInput.toString()
+ '\n' +'function doNewField(o){'
+ '\n' +' var sSqlString = "";'
+ '\n' +' if(o.fieldName.value.length==0){alert("请输入字段名");o.fieldName.focus();return false;}'
+ '\n' +' if(!checkInput(o.fieldName.value)){alert("名称中包含非法字符,请重新输入");return false;}'
+ '\n' +' sSqlString = "ALTER TABLE ["+o.sTblName.value+"] \\n"'
+ '\n' +' + " ADD COLUMN ["+o.fieldName.value+"] "+o.fieldType[o.fieldType.selectedIndex].value'
+ '\n' +' + (o.fieldSize.value?"("+o.fieldSize.value+")":"") + " "'
+ '\n' +' + (o.isNULL.checked?"NULL":"NOT NULL") + " \\n"'
+ '\n' +' + (o.fieldPK.checked?"PRIMARY KEY":"") + " "'
+ '\n' +' + (parseInt(o.fieldSeed.value)&&parseInt(o.fieldIncre.value)?"IDENTITY ("+o.fieldSeed.value+","+o.fieldIncre.value+")":"") + " \\n"'
+ '\n' +' + (o.fieldDefault.value?"DEFAULT "+o.fieldDefault.value+" WITH VALUES":"");'
+ '\n' +' var oFormSQL = window.opener.getObjByID("formSQL");'
+ '\n' +' oFormSQL.sSqlString.value = sSqlString;'
+ '\n' +' oFormSQL.sOper.value = "Design";'
+ '\n' +' if(!confirm("确认要添加新列吗?")) return false;'
+ '\n' +' oFormSQL.submit();'
+ '\n' +'}'
+ '\n' +'</scrip'+'t>'
+ '\n' +'<table width="500" border="1" cellspacing="0" cellpadding="0">'
+ '\n' +' <form name="formAddFld" id="formAddFld" method="post" onSubmit="doNewField(this);return false;">'
+ '\n' +' <tr> '
+ '\n' +' <td colspan="2">为表 [table name] 添加新字段</td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td width="30%">字段名</td>'
+ '\n' +' <td><input name="fieldName" type="text" id="fieldName" size="30"></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>类型</td>'
+ '\n' +' <td>'
+ '\n' +'<select id="fieldType" name="fieldType" onChange="checkFieldType(this.value,this[this.selectedIndex].sLength)" onBlur="showFieldType(0)" onFocus="window.currElement=this;" style="width:130">'
+ '\n' +'<OPTION value=TinyInt sLength=1>TinyInt'
+ '\n' +'<OPTION value=SmallInt sLength=1>SmallInt'
+ '\n' +'<OPTION value=Int sLength=1>Int'
+ '\n' +'<OPTION value=BigInt sLength=1>BigInt'
+ '\n' +'<OPTION value=Real sLength=1>Real / Single'
+ '\n' +'<OPTION value=Float sLength=1>Float / Double'
+ '\n' +'<OPTION value=Money sLength=1>Money'
+ '\n' +'<OPTION value=Decimal sLength=1>Decimal'
+ '\n' +'<OPTION value=Numeric sLength=1>Numeric'
+ '\n' +'<OPTION value=Bit sLength=1>Bit'
+ '\n' +'<OPTION value=Variant>Variant'
+ '\n' +'<OPTION value=UniqueIdentifier sLength=1>UniqueIdentifier'
+ '\n' +'<OPTION value=DateTime sLength=1>DateTime'
+ '\n' +'<OPTION value=Char selected>Char'
+ '\n' +'<OPTION value=VarChar>VarChar'
+ '\n' +'<OPTION value=Text sLength=1>Text'
+ '\n' +'<OPTION value=nChar>nChar'
+ '\n' +'<OPTION value=nVarChar>nVarChar'
+ '\n' +'<OPTION value=nText sLength=1>nText'
+ '\n' +'<OPTION value=TimeStamp sLength=1>TimeStamp'
+ '\n' +'<OPTION value=VarBinary sLength=1>VarBinary'
+ '\n' +'<OPTION value=Image sLength=1>Image'
+ '\n' +'</select>'
+ '\n' +' </td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>宽度</td>'
+ '\n' +' <td><input name="fieldSize" type="text" id="fieldSize" value="50" size="30"></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>允许空</td>'
+ '\n' +' <td><input name="isNULL" type="checkbox" id="isNULL" value="checkbox" checked></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>主键</td>'
+ '\n' +' <td><input name="fieldPK" type="checkbox" id="fieldPK" value="PRIMARY KEY"></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>递增</td>'
+ '\n' +' <td>种子数 <input name="fieldSeed" type="text" id="fieldSeed" size="5">'
+ '\n' +' 递增量 '
+ '\n' +' <input name="fieldIncre" type="text" id="fieldIncre" size="5"></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr> '
+ '\n' +' <td>默认值</td>'
+ '\n' +' <td><input name="fieldDefault" type="text" id="fieldName4" size="30"></td>'
+ '\n' +' </tr>'
+ '\n' +' <tr>'
+ '\n' +' <td> </td>'
+ '\n' +' <td align="right">'
+ '\n' +' <input type="hidden" name="sTblName" value="[table name]">'
+ '\n' +' <input type="submit" name="Submit2" value="确定">'
+ '\n' +' <input name="Reset" type="reset" id="Reset" value="关闭" onClick="window.close();"></td>'
+ '\n' +' </tr>'
+ '\n' +' </form>'
+ '\n' +'</table>';
var CCNS_program = "COCOON DB Tools";
var CCNS_version = "Final Release";
function getObjByID(n) {
//This function was re-written form DreamWeaver v4.01 by Sunrise_Chen.
var p,i,x;
var d=document;
if(!(x=d[n])&&d.all) x=d.all[n];
for(i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=getObjByID(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n);
return x;
}
function doClick(o){
var sObjName, sObjType, sSqlString;
var oFormSql = getObjByID("formSQL");
for(var i=0;i<o.form.objName.length;i++){
if(o.form.objName[i].checked){
sObjName = o.form.objName[i].objName;
sObjType = o.form.objName[i].objType;
break;
}
}
switch(o.value){
case "查看":
if(!sObjName){alert("请选择对象"); return false;}
sSqlString = "SELECT * FROM [" + sObjName + "]";
oFormSql.sTblName.value = sObjName;
oFormSql.sTblType.value = sObjType;
oFormSql.sSqlString.value = sSqlString;
oFormSql.sOper.value = "Select";
oFormSql.submit();
break;
case "删除":
if(!sObjName){alert("请选择对象"); return false;}
switch(sObjType){
case "TABLE" :
sSqlString = "DROP TABLE [" + sObjName + "]";
break;
case "VIEW" :
sSqlString = "DROP VIEW [" + sObjName + "]";
break;
}
oFormSql.sSqlString.value = sSqlString;
if(!confirm("你确认要删除 "+sObjName+' 对象吗?')) break;
oFormSql.sTblName.value = "";
oFormSql.sTblType.value = "";
oFormSql.sOper.value = "";
oFormSql.submit();
break;
case "新建表":
var sObjName = prompt("请输入表名:",'');
while(!checkInput(sObjName)){alert('名称中包含非法字符,请重新输入');sObjName = prompt("请输入表名",'');}
if(sObjName){
sSqlString = "CREATE TABLE ["+sObjName+"] ( \n"
+ "ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED \n"
+ ")"
oFormSql.sTblName.value = sObjName;
oFormSql.sTblType.value = "TABLE";
oFormSql.sSqlString.value = sSqlString;
if(!confirm("你确认要新建 "+sObjName+' 表吗?')) break;
oFormSql.submit();
}
break;
case "设计":
if(!sObjName){alert("请选择对象"); return false;}
sSqlString = "SELECT * FROM [" + sObjName + "]";
oFormSql.sTblName.value = sObjName;
oFormSql.sTblType.value = sObjType;
//oFormSql.sSqlString.value = sSqlString;
oFormSql.sSqlString.value = "";
oFormSql.sOper.value = "Design";
oFormSql.submit();
break;
case "显示所有对象" :
oFormSql.sOper.value = "";
oFormSql.sSqlString.value = "";
oFormSql.sTblName.value = "";
oFormSql.sTblType.value = "";
oFormSql.submit();
break;
case "清空" :
if(sObjType!="TABLE"){alert("你不能清空该对象。");break;}
oFormSql.sOper.value = "";
oFormSql.sSqlString.value = "DELETE * FROM ["+sObjName+"]";
oFormSql.sTblName.value = sObjName;
oFormSql.sTblType.value = sObjType;
if(!confirm("你确认要清空 "+sObjName+' 表吗?')) break;
oFormSql.submit();
break;
case "新建查询" :
sTblName = prompt("请输入查询名称:","")
while(!checkInput(sTblName)){alert('名称中包含非法字符,请重新输入');sTblName = prompt("请输入表名",'');}
if(!sTblName) return false;
sSqlString = "CREATE VIEW ["+sTblName+"] \nAS \n"
oFormSql.sSqlString.value = sSqlString;
alert("请在SQL框里将语句填写完整。");
oFormSql.sSqlString.focus();
}
}
function showFieldType(o){
var oDivType = getObjByID("divFieldType");
var oFieldType = getObjByID("selFieldType");
if(!o){if(oDivType) oDivType.style.display='none'; return;}
if(!oFieldType) return;
window.currElement = o;
oFieldType.value = o.value;
oDivType.style.top = event.y + document.body.scrollTop - event.offsetY -4 ;
oDivType.style.left = event.x + document.body.scrollLeft - event.offsetX -4 ;
oDivType.style.display="";
oFieldType.focus();
}
function checkFieldType(s,i){
if(window.currElement){
window.currElement.value = s;
if(i) window.currElement.form.fieldSize.value = "";
}
}
function showDetial(o){
if(!o) return;
var iSW = parseInt(screen.AvailWidth);
var iSH = parseInt(screen.AvailHeight);
iW = 300;
iH = 100;
var newWin = open('about:blank','','width='+iW+',height='+iH+',top='+((iSH/2)-(iH/2))+',left='+((iSW/2)-(iW/2)));
newWin.document.title = CCNS_program + " - Detial Page ";
newWin.document.body.innerHTML = o.title;
}
function doOper(o,sTblName){
var iSW = parseInt(screen.AvailWidth);
var iSH = parseInt(screen.AvailHeight);
iW = 520;
iH = 260;
var oFormSql = getObjByID("formSQL");
var sSqlString = "";
switch(o.value){
case "新增" :
var newWin = open('about:blank','','width='+iW+',height='+iH+',top='+((iSH/2)-(iH/2))+',left='+((iSW/2)-(iW/2)));
newWin.document.write(sNewFieldString.replace(/\[table name\]/g,sTblName));
newWin.document.title = CCNS_program + " - New Field ";
break;
case "删除" :
sSqlString = "ALTER TABLE [" + sTblName + "] \n"
+ " DROP COLUMN [" + o.form.fieldName.value + "]";
oFormSql.sSqlString.value = sSqlString;
if(!confirm("确认要删除“"+o.form.fieldName.value+"”列吗?")) return false;
oFormSql.sOper.value = "Design";
oFormSql.submit();
break;
case "修改" :
sSqlString = "ALTER TABLE [" + sTblName + "] \n"
+ " ALTER COLUMN [" + o.form.fieldName.value + "] "
+ o.form.fieldType.value + " "
+ (o.form.fieldSize.value.length?"("+o.form.fieldSize.value+")":"") + " "
+ (o.form.fieldNull.checked?"NULL":"NOT NULL");
oFormSql.sSqlString.value = sSqlString;
if(!confirm("确认要修改“"+o.form.fieldName.value+"”列吗?")) return false;
oFormSql.sOper.value = "Design";
oFormSql.submit();
break;
case "新记录" :
iW = 600;
iH = 480;
var newWin = open('about:blank','','scrollbars=yes,width='+iW+',height='+iH+',top='+((iSH/2)-(iH/2))+',left='+((iSW/2)-(iW/2)));
newWin.document.write(sNewRecString);
newWin.document.title = CCNS_program + " - New Record ";
break;
case "删" :
if(o.tblName.length<1||o.sCondition.length<1){alert("条件不正确,不能删除。");return false;}
sSqlString = "DELETE * FROM ["+o.tblName+"] WHERE "+o.sCondition;
oFormSql.sSqlString.value = sSqlString;
if(!confirm("确认要删除吗?")) return false;
oFormSql.submit();
break;
}
}
function changePage(o,i){
var oFormSql = getObjByID("formSQL");
switch(i){
case 0 :
oFormSql.PageNo.value = o.form.gotoPage.value;
break;
case 1 :
oFormSql.PageNo.value = 1;
break;
case 2 :
oFormSql.PageNo.value = parseInt(o.form.PageNo.value)-1;
break;
case 3 :
oFormSql.PageNo.value = parseInt(o.form.PageNo.value)+1;
break;
case 4 :
oFormSql.PageNo.value = 9999;
break;
}
oFormSql.submit();
}
function doNewRec(o){
var sSqlString = "INSERT INTO ["+o.tblName.value+"] ("
for(var i=0;i<o.length;i++){
if(o[i].type!="hidden"&&o[i].type!="submit"){
sSqlString += "[" + o[i].name + "], "
}
}
sSqlString = sSqlString.substr(0,sSqlString.length-2) + ") VALUES("
for(var i=0;i<o.length;i++){
if(o[i].type!="hidden"&&o[i].type!="submit"){
sSqlString += "'" + o[i].value + "', ";
}
}
sSqlString = sSqlString.substr(0,sSqlString.length-2) + ") "
var oFormSQL = window.opener.getObjByID("formSQL");
oFormSQL.sSqlString.value = sSqlString;
if(!confirm("确认提交吗?")) return false;
oFormSQL.submit();
}
function clearSubmitForm(o){
var oFormSQL = getObjByID("formSQL");
oFormSQL.sTblName.value = "";
oFormSQL.sTblType.value = "";
oFormSQL.sOper.value = "";
}
function doSelField(o){
var bSelect = false;
var oFormSQL = getObjByID("formSQL");
var oForm = o;
var sSqlString = "SELECT "
for(var i=0;i<oForm.fieldName.length;i++){
if(oForm.fieldName[i].checked){
sSqlString += "[" + oForm.fieldName[i].value + "],";
bSelect = true;
}
}
sSqlString = sSqlString.substr(0,sSqlString.length-1);
if(!bSelect) sSqlString += " *";
sSqlString += (" FROM " + "[" + oForm.sTblName.value + "] ");
if(oForm.orderField.selectedIndex>0){
sSqlString += "ORDER BY [" + oForm.orderField[oForm.orderField.selectedIndex].value + "]";
sSqlString += " " + oForm.orderBy[oForm.orderBy.selectedIndex].value;
}
oFormSQL.sSqlString.value = sSqlString;
oFormSQL.submit();
}
function checkInput(s){
if(s.indexOf('"')>=0||s.indexOf(' ')>=0)
return false;
return true;
}
2010-05-31
Settingshezhi.settings reportViewer1 Report1.rdlc DataSet.xsd C# .NET FRAMEWORK 源代码
OleDbConnection Con = null;
string strCon = null;
OleDbCommand Cmd = null;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\Administrator\\桌面\\江大软件\\28.mdb";
Con = new OleDbConnection(strCon);//建立连接
string strSql = "select * from BZData where GroupID='1214015'";//表名的写法也应注意不同,对应的excel表为sheet1,在这里要在其后加美元符号$,并用中括号
Cmd = new OleDbCommand(strSql, Con);//建立要执行的命令
OleDbDataReader read = null;
DataSet ds = new DataSetshujuji();
try
{
Con.Open();
//从命令对象中读取数据
read = Cmd.ExecuteReader();
//有了ADO.NET,可把读取来的数据直接加载到数据集中
ds.Tables[0].Load(read);
read.Close();
Con.Close();
//为查看器提供本地报表数据
CrystalReport1 cry = new CrystalReport1();
cry.SetDataSource(ds);
crystalReportViewer1.ReportSource = cry;
}
catch (Exception ex) { }
this.reportViewer1.RefreshReport();
哈哈
2010-05-31
CrystalReport1.rpt ,DataSet.xsd,crystalReportViewer1水晶报表,以及向文本框对象传值 C# .NET FRAMEWORK 源代码 决定对您有用
OleDbConnection Con = null;
string strCon = null;
OleDbCommand Cmd = null;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\Administrator\\桌面\\江大软件\\28.mdb";
Con = new OleDbConnection(strCon);//建立连接
string strSql = "select * from BZData where GroupID='1214015'";//表名的写法也应注意不同,对应的excel表为sheet1,在这里要在其后加美元符号$,并用中括号
Cmd = new OleDbCommand(strSql, Con);//建立要执行的命令
OleDbDataReader read = null;
DataSet ds = new DataSetshujuji(); try
{
Con.Open();
//从命令对象中读取数据
read = Cmd.ExecuteReader();
//有了ADO.NET,可把读取来的数据直接加载到数据集中
ds.Tables[0].Load(read);
read.Close();
Con.Close();
//为查看器提供本地报表数据
CrystalReport1 cry = new CrystalReport1();((TextObject)cry.ReportDefinition.ReportObjects["Text12"]).Text = "sdsad";
//ParameterRangeValue
cry.SetDataSource(ds);
crystalReportViewer1.ReportSource = cry;
}
catch (Exception ex) { }
}
}
}
2010-05-31
xls转mdb代码以及.exe执行软件
******* 导出到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)
--sample XML document
SET @doc ='
Customer was very satisfied
Important
Happy Customer.
'
-- Create an internal representation of 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
???????
/**********************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)
--首先将excel表内容导入到一个全局临时表
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)
--然后用bcp从全局临时表导出到文本文件
set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql
--删除临时表
exec('drop table '+@tbname)
/********************导整个数据库*********************************************/
用bcp实现的存储过程
/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
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
*/
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外,均不支持非本机数据导入
2010-05-09
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人