/*
SQL Server Row Script Creator
SURESH October 2010
This script will generate script to insert/update from a source table in one database to an
identical destination table in another database or server. It can be run for inserts or updates,
and can be run for a single row in insert and update mode, or all rows in a table for insert mode.
*/
declare @tab varchar(50)
,@pk1Val varChar(10)
,@pk1Name varChar(50)
,@qt char(1)
,@StatementType varChar(10)
set nocount on
/*
Instructions:
1) open script and connect to the source database
2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)
3) execute the script (best to use text output)
4) copy the script output into a script window, and run on the destination table.
@Tab = the name of the source table
@pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row
@pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key
@StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement
*/
select @tab = 'hari', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT'
declare @tabName varchar(50)
, @colName varchar(50)
, @colType varchar(50)
, @collength varChar(50)
, @colOrder int
, @IsIdent char(1)
create table #output (Line varChar(4000), LineOrder int)
create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))
declare @out varchar(4000)
,@lineCounter int
,@ColValue varchar(4000)
,@sortCol varchar(50)
/* get the ordering column */
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 ))
/* put in the repeating values based on the columns*/
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
--select @numCols --debug code
open objCurs
Fetch from objCurs
into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder
while @@fetch_status = 0
begin
SET @counter = 0
/* get the value from the table */
if @IsIdent = 'N'
BEGIN
--select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code
/* increase better type handling by inserting more case statments, handling different data types */
if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0
begin
/* getting all rows in the table */
exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)
select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +
' declare @counter int set @counter = 0 ' +
' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )
end
else
begin
/* filtering by a pk val */
exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)
select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +
' where ' + @pk1Name + ' = ' + @pk1Val)
end
end /* if @isIdent = 'n' */
Fetch Next from objCurs
into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder
end
--select * from #ColumnValues --debug code
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
/* next cursor for outputting the results from the retval table into the output table */
declare ColVal_Curs cursor for
select ColName , ColOrder , RowNumber , ColValue , colType
from #ColumnValues
order by RowNumber, ColOrder
open ColVal_Curs
--set the last row number to the first in the table, so post loop checking works
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 /* cursor loop */
/* get the quote type to enclose the value from the column type */
select @qt = case @colType
when 'nvarchar' then ''''
when 'nchar' then ''''
when 'DateTime' then ''''
when 'ntext' then ''''
when 'varchar' then ''''
else ''
end
--select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType
if not @ColValue is null
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)
end
if @statementType = 'INSERT'
BEGIN
/* put comma in */
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 = ''
/* put in the value of the column */
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 = ''
/* get the next record*/
fetch from ColVal_Curs into
@colName, @ColOrder, @RowNumber, @colValue, @ColType
--select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code
if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)
BEGIN
/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/
/* ie we are building a record to insert, and the PK changes because we are at the next record */
/* remove the last comma from the last line */
declare @lastLine int
if @statementType = 'UPDATE'
begin
/*remove last comma*/
update #output
set Line = left(Line,datalength(Line)-1)
where lineOrder = @LineCounter
/* insert a 'where' clause */
insert into #output (line, LineOrder)
select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output
end
if @statementType = 'INSERT'
BEGIN
/* put in a 'values' statement between the column names and the column values */
insert into #output (Line, LineOrder)
values (') VALUES (', @LastRowNumber + @numCols + 5)
/* close off the lot */
insert into #output (line, lineorder)
select ')', Max(LineOrder) + 1 from #output
END
set @lastRowNumber = @RowNumber
set @lineCounter = @RowNumber /* reset linecounter for next set */
End /* if rownumber > last row number */
end /* cursor loop */
close objCurs
deallocate objCurs
close ColVal_Curs
deallocate ColVal_Curs
/* get the statements out from the list*/
select line as [Copy script from output window below] from #output order by lineorder
/* bug tracking code - uncomment to diagnose problems
select distinct RowNumber from #ColumnValues order by 1
select * from #ColumnValues
order by RowNumber, ColOrder, ColName, ColValue
*/
drop table #output
drop table #ColumnValues
set nocount off
No comments:
Post a Comment