<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
在A服务器中有表table1,想把该表的内容导到B服务器中,这两个没联网,怎么办?
导成外部文件,再用导入导出。。。。这太笨了吧,如果对方服务器是INTERNET上的,只能执行服本怎么办,晕死了吧,用这个存储过程来帮你。
ALTER procsp_GetTableData
@objnamenvarchar(776)
as
--set@objname='s_coderule'
/*declare@novarchar(35),@yesvarchar(35),@nonevarchar(35)
select@no=namefrommaster.dbo.spt_valueswheretype='B'andnumber=0
select@yes=namefrommaster.dbo.spt_valueswheretype='B'andnumber=1
select@none=namefrommaster.dbo.spt_valueswheretype='B'andnumber=2
*/
declare@numtypesnvarchar(80)
select@numtypes=N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,varbinary,bit'
declare@chartypesnvarchar(80)
select@chartypes=N'char,varchar,text,nchar,ntext,nvarchar'
declare@datetypesnvarchar(80)
select@datetypes=N'datetimesmalldatetime'
declare@LimitlengthInt,@Limitlength1Int
declare@objidbigint
--declare@sysobj_typechar(2)
select@objid=id
--,@sysobj_type=xtype
fromsysobjectswhereid=object_id(@objname)
set@Limitlength=0
set@limitLength1=0
declare@namevarchar(30),@typevarchar(10),@lengthint,@FieldSQLvarchar(5000),@SQLvarchar(8000),@identsmallint
set@SQL=''
set@FieldSQL=''
declarecurcursor
for
select
'Column_name' =name,
'Type' =type_name(xusertype),
'Length' =convert(int,length),
'Ident' =colstat
fromsyscolumnswhereid=@objidandnumber=0orderbycolid
openCur
fetchnextfromcurinto@name,@type,@length,@ident
while@@fetch_status=0
begin
if@Ident=0
begin
if@Limitlength>200
begin
set@sql=@sql+','
set@limitlength=0
end
if@Limitlength1>200
begin
set@Fieldsql=@Fieldsql+char(39)+','+char(39)
set@limitlength1=0
end
Set@FieldSQl=@FieldSQL+LTrim(Rtrim(@name))+','
if charindex(@type,@numtypes)>0--('+convert(char(6),@length)+')
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''elseRtrim(Convert(char,'+@name+'))end'
elseifcharindex(@type,@chartypes)>0
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''else+char(39)+RTrim('+@name+')+char(39)end'
elseifcharindex(@type,@datetypes)>0
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''else+char(39)+RTrim(convert(char(19),'+@name+',120))+char(39)end'
set@SQL=@SQL+'+'+char(39)+','+char(39)
set@Limitlength=@Limitlength+@Length 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
在A服务器中有表table1,想把该表的内容导到B服务器中,这两个没联网,怎么办?
导成外部文件,再用导入导出。。。。这太笨了吧,如果对方服务器是INTERNET上的,只能执行服本怎么办,晕死了吧,用这个存储过程来帮你。
ALTER procsp_GetTableData
@objnamenvarchar(776)
as
--set@objname='s_coderule'
/*declare@novarchar(35),@yesvarchar(35),@nonevarchar(35)
select@no=namefrommaster.dbo.spt_valueswheretype='B'andnumber=0
select@yes=namefrommaster.dbo.spt_valueswheretype='B'andnumber=1
select@none=namefrommaster.dbo.spt_valueswheretype='B'andnumber=2
*/
declare@numtypesnvarchar(80)
select@numtypes=N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,varbinary,bit'
declare@chartypesnvarchar(80)
select@chartypes=N'char,varchar,text,nchar,ntext,nvarchar'
declare@datetypesnvarchar(80)
select@datetypes=N'datetimesmalldatetime'
declare@LimitlengthInt,@Limitlength1Int
declare@objidbigint
--declare@sysobj_typechar(2)
select@objid=id
--,@sysobj_type=xtype
fromsysobjectswhereid=object_id(@objname)
set@Limitlength=0
set@limitLength1=0
declare@namevarchar(30),@typevarchar(10),@lengthint,@FieldSQLvarchar(5000),@SQLvarchar(8000),@identsmallint
set@SQL=''
set@FieldSQL=''
declarecurcursor
for
select
'Column_name' =name,
'Type' =type_name(xusertype),
'Length' =convert(int,length),
'Ident' =colstat
fromsyscolumnswhereid=@objidandnumber=0orderbycolid
openCur
fetchnextfromcurinto@name,@type,@length,@ident
while@@fetch_status=0
begin
if@Ident=0
begin
if@Limitlength>200
begin
set@sql=@sql+','
set@limitlength=0
end
if@Limitlength1>200
begin
set@Fieldsql=@Fieldsql+char(39)+','+char(39)
set@limitlength1=0
end
Set@FieldSQl=@FieldSQL+LTrim(Rtrim(@name))+','
if charindex(@type,@numtypes)>0--('+convert(char(6),@length)+')
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''elseRtrim(Convert(char,'+@name+'))end'
elseifcharindex(@type,@chartypes)>0
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''else+char(39)+RTrim('+@name+')+char(39)end'
elseifcharindex(@type,@datetypes)>0
set@sql=@SQL+'+Casewhen'+@name+'isnullthen''Null''else+char(39)+RTrim(convert(char(19),'+@name+',120))+char(39)end'
set@SQL=@SQL+'+'+char(39)+','+char(39)
set@Limitlength=@Limitlength+@Length 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>