将SQL2005远程数据库复制到本地SQL2000 [英] Copy SQL2005 remote database to local SQL2000
本文介绍了将SQL2005远程数据库复制到本地SQL2000的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将表从远程数据库sqlserver 2005复制到sql server 2000到本地系统中.
我该怎么做?
I want to copy a table from remote database sqlserver 2005 to sql server 2000 into the local system.
How can I do this?
推荐答案
请参阅此文章 [ ^ ].
另一种方法是备份并还原到本地服务器.
See this article[^].
Another way could be to take a backup and restore into your local server.
朋友，
您可以使用插入查询生成脚本将表记录从sql server2005移到sqlserver2000
代码:-
Hi Friend,
You can use Insert Query generation script to move your table records from sql server2005 to sqlserver2000
Code:-
declare @tab varchar(50)
,@pk1Val varChar(100)
,@pk1Name varChar(50)
,@qt char(1)
,@StatementType varChar(10)
,@tableWhereClause varchar(255)
,@ignoreIdentityCol bit
,@owner varchar(20)
set nocount on
select @tab = 'Your Table Name ', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT', @ignoreIdentityCol = 1, @owner = 'dbo'
select @tableWhereClause = ''
declare @tabName varchar(50)
, @colName varchar(50)
, @colType varchar(50)
, @collength varChar(50)
, @colOrder int
, @IsIdent char(1)
, @wasIdent bit
if not (@owner = '')
set @owner = @owner + '.'
create table #output (Line varChar(4000), LineOrder int, rowNumber int)
create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))
declare @out varchar(8000)
,@lineCounter int
,@ColValue varchar(8000)
,@sortCol varchar(50)
select @sortCol = sc.Name
from sysobjects so
inner join syscolumns sc
on so.id= sc.id
inner join systypes st
on sc.xtype = st.xusertype
where so.Name = @tab
and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))
declare objCurs CURSOR FOR
select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder
from sysobjects so
inner join syscolumns sc
on so.id= sc.id
inner join systypes st
on sc.xtype = st.xusertype
where so.Name = @tab
DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int
select @numCols = count(sc.id)
from sysobjects so
inner join syscolumns sc
on so.id= sc.id
where so.Name = @tab
open objCurs
Fetch from objCurs
into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder
while @@fetch_status = 0
begin
SET @counter = 0
if @IsIdent = 'N' or @ignoreIdentityCol = 1
BEGIN
if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0
begin
exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)
select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @owner + @tabName + ' ' + @tableWhereClause + ' order by ' + @SortCol + ' ' +
' declare @counter int set @counter = 0 ' +
' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )
end
else
begin
exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)
select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @owner + @tabName +
' where ' + @pk1Name + ' = ' + @pk1Val)
end
end
if @IsIdent = 'Y'
set @wasIdent = 1
Fetch Next from objCurs
into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder
end
select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished
select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber
declare ColVal_Curs cursor for
select ColName , ColOrder , RowNumber , ColValue , colType
from #ColumnValues
order by RowNumber, ColOrder
declare @curRowNum int, @curLineNum int
open ColVal_Curs
select @lastRowNumber = min(rowNumber) from #ColumnValues
set @lineCounter = @LastRowNumber --initialise at the first row
fetch from ColVal_Curs into
@colName, @ColOrder, @RowNumber, @colValue, @ColType
while @@Fetch_status = 0
BEGIN
select @qt = case @colType
when 'nvarchar' then ''''
when 'nchar' then ''''
when 'varchar' then ''''
when 'char' then ''''
when 'DateTime' then ''''
when 'ntext' then ''''
else ''
end
if not @ColValue is null
SET @ColValue = replace(@ColValue, '''', '''''')
else
if @qt = ''''
set @qt = ''
if @rowNumber = @lineCounter
select @out = case @statementType
when 'UPDATE' THEN 'Update ' + @tab + ' SET '
when 'INSERT' then 'INSERT INTO ' + @tab + ' ('
end
begin
if @StatementType = 'UPDATE'
BEGIN
select @Out = @out + @colName + ' = ' + @qt + COALESCE(@ColValue, 'NULL') + @qt + ',' -- + @ColType
insert into #output (Line, LineOrder)
values (@out, @lineCounter)
if @pk1Val = ''
if @pk1Name = @colName
select @pk1Val = @qt + @colValue + @qt
end
if @statementType = 'INSERT'
BEGIN
if @lineCounter > @RowNumber --not first line in set of values for row
select @out = @out + ','
/*put in the name of the column */
insert into #output (Line, LineOrder)
values (@out + @colName
, @lineCounter)
if @lineCounter > @RowNumber --not first line in set of values for row
select @out = ','
else
select @out = ''
insert into #output (Line, LineOrder)
values (@out + @qt + COALESCE(@ColValue, 'NULL') + @qt
, @lineCounter + 10 + @numCols)
END
end /*not @ColValue is null */
select @lineCounter = @lineCounter + 1
set @out = ''
set @curRowNum = @rowNumber
set @curLineNum = @lineCounter -1
fetch from ColVal_Curs into
@colName, @ColOrder, @RowNumber, @colValue, @ColType
if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)
BEGIN
declare @lastLine int
if @statementType = 'UPDATE'
begin
update #output
set Line = left(Line,datalength(Line)-1)
where lineOrder = @curLineNum
insert into #output (line, LineOrder)
select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output
end
if @statementType = 'INSERT'
BEGIN
insert into #output (Line, LineOrder)
values (') VALUES (', @curRowNum + @numCols + 5)
insert into #output (line, lineorder)
select ')', Max(LineOrder) + 1 from #output
END
set @lastRowNumber = @RowNumber
set @lineCounter = @RowNumber /* reset linecounter for next set */
update #output
set RowNumber = @currownum
where RowNumber is null
End
end
close objCurs
deallocate objCurs
close ColVal_Curs
deallocate ColVal_Curs
create table #combineOutput (rowNumber int, line varchar(4000))
if @ignoreIdentityCol = 1 and @wasIdent = 1
insert into #combineOutput (rowNumber, line)
values (-1000, 'set identity_insert ' + @tabName + ' on')
declare @output varchar(8000), @codeLine varchar(4000), @thisRowNum int, @lastRowNum int
select @output = ''
declare line_curs cursor for
select line, RowNumber from #output order by RowNumber, lineorder
open line_curs
fetch from line_curs into @codeLine, @thisRowNum
select @lastRowNum = @thisRowNum
while @@fetch_status = 0
begin
if @thisROwNum > @lastRowNum
BEGIN
insert into #combineOutput (rowNumber, line) values (@rowNumber, @output)
set @output = ''
END
select @output = @output + @codeLine + ' '
select @lastRowNum = @thisRowNum
fetch from line_curs into @codeLine, @thisRowNum
end
insert into #combineOutput (rowNumber, line) values (@rowNumber, @output)
close line_curs
deallocate line_curs
if @ignoreIdentityCol = 1 and @wasIdent = 1
insert into #combineOutput (rowNumber, line)
values (10000000, 'set identity_insert ' + @tabName + ' off')
select line as [/*Copy and paste code from below*/] from #combineOutput order by rowNumber
drop table #output
drop table #combineOutput
drop table #ColumnValues
set nocount off
just give Your table name in the above script.
Insert Query will be generated
Run the generated script in sql 2000 to move your table data from sql server 2005
--Thank You,
Please keep me in touch..
--Regards
阿拉文斯·G
SQL Developer，
Kadamba Technologies,
钦奈
Email:- aravinth.it04@gmail.com
just give Your table name in the above script.
Insert Query will be generated
Run the generated script in sql 2000 to move your table data from sql server 2005
--Thank You,
Please keep me in touch..
--Regards
Aravinth.G
SQL Developer,
Kadamba Technologies,
Chennai
Email:- aravinth.it04@gmail.com
No just mention your table name and execute this script in sql server2005.
you can get insert queries for ur table. just copy it and paste it in sql server2000 and excute it
No just mention your table name and execute this script in sql server2005.
you can get insert queries for ur table. just copy it and paste it in sql server2000 and excute it
这篇关于将SQL2005远程数据库复制到本地SQL2000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
