Copy SQL2005 remote database to local SQL2000

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
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

Use the import /export wizard.
It will easily copy the table.

Thanks