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
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
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 + ','
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
select @out = @out + ','
insert into #output (Line, LineOrder)
values (@out + @colName
, @lineCounter)
if @lineCounter > @RowNumber
select @out = ','
else
select @out = ''
insert into #output (Line, LineOrder)
values (@out + @qt + COALESCE(@ColValue, 'NULL') + @qt
, @lineCounter + 10 + @numCols)
END
end
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
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 [] 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