February 3, 2003 at 9:24 am
I need to update a development and test database with data from the production database.
The development version has new tables, other objects and data. Therefore, backup restore is not a good option. This database has over 100 tables. So creating inserts for each table manually would be very labor intensive.
I was thinking of creating a script that would dynamically find all tables in the production that had any records in it and insert this into the development version database. Does this sound viable. What about all of the incremental id fields?
Any ideas would be appreciated.
February 4, 2003 at 6:45 am
I believe it is;
reason being, I have created a script ( not perfected ) that takes referential integrity, and identity columns into account and creates a sample database i.e. Schematically the same, but with only a specified percentage of data.
February 5, 2003 at 5:54 am
It works, but I am sure there are better ways to address some of the issues. I will try to bring in the code for this tomorrow and send it through to you.
February 5, 2003 at 9:19 am
That would be great. I would like to compare it with what I have put together. I will pass along what my final version looks like so you can compare as well.
February 7, 2003 at 1:07 am
Sorry about the delay: you'll obviously have to make some changes notably the 'Sampledb' has to be changed to your database name
Try it with northwind or pubs first. Also it need to be run from the source database this can obviously be changed and that's why "not perfected". Hope it helps.
set nocount on
declare @l tinyint
,@tab sysname
,@percentage decimal
,@rowid int
,@maxlevel int
,@fkeyid int
,@rkeyid int
,@rtab sysname
,@fcolstr nvarchar( 4000 )
,@rcolstr nvarchar( 4000 )
,@fcolid int
,@rcolid int
,@fcol sysname
,@rcol sysname
,@join varchar(4000)
,@jc varchar(4000)
,@runSQL varchar(8000)
,@alias int
,@requiredrows int
,@tabowner sysname
,@tablevel tinyint
,@collist varchar( 8000 )
--Define sample size required
Select @percentage = 10
Create table #tables(
rowid int identity( 1, 1 ),
tabname sysname,
tabowner sysname,
tabid int,
tablevel tinyint,
rowcnt int constraint def_rowcnt default 0
,idcolumn bit
constraint pk_tables primary key clustered( tabid )
)
insert into #tables
( tabname, tabowner, tabid, tablevel )
select distinct
o.name, user_name( uid ), o.id, 0
from sysobjects as o
where o.xtype = 'u' and o.name <> 'dtproperties'
order by o.name
--===================================================================================
-- add row count figures
select @rowid = max( rowid ) from #tables
while @rowid > 0 begin
select @tab = tabname from #tables where rowid = @rowid
-- print @tab
exec( '
update #tables
set rowcnt = (select count(*) from [' +@tab+ '])
where tabname = ''' +@tab+ '''
')
set @rowid = @rowid - 1
end
--====================================================================================
-- Create dependency levels
set @l = 2
update #tables
set tablevel = 1
From #tables as o
left join sysreferences as r
on r.fkeyid = o.tabid
where r.fkeyid is null
while @l < 32 begin
--select * from #tables order by tabname
update o
set tablevel = @l
From #tables as o
inner join sysreferences as r
on r.fkeyid = o.tabid
inner join #tables as b
on r.rkeyid = b.tabid
where b.tablevel = @l - 1
set @l = @l + 1
end
--======================================================================================
-- clean target tables
select @l = max( tablevel)
from #tables
While @l >= 0 begin
Select @rowid = max(rowid)
from #tables
Where tablevel = @l
While @rowid <> 0 begin
Select @tab = tabname
,@tabowner = tabowner
from #tables
Where rowid = @rowid
Select @RunSQL =
'delete from [SampleDB].['+ @tabowner +'].['+ @tab +'] '+char( 10 )
Exec( @RunSQL )
Select @rowid = max( rowid )
From #tables
Where rowid < @rowid
And tablevel = @l
End
if @l = 0 break
Set @l = @l -1
End
--======================================================================================
-- set column references
drop table #t
create table #t
( rowid int identity(1, 1)
,fkid int not null
,constid int
,fkcolid nvarchar( 1024 )
,rkid int
,rkcolid nvarchar( 1024 )
)
insert into #t ( fkid, constid, fkcolid, rkid, rkcolid )
select tabid
,constid
,cast( [fkey1] as nvarchar(4) ) + ', ' +
isnull(
(
cast( [fkey2] as nvarchar(4) ) + ', ' +
cast( [fkey3] as nvarchar(4) ) + ', ' +
cast( [fkey4] as nvarchar(4) ) + ', ' +
cast( [fkey5] as nvarchar(4) ) + ', ' +
cast( [fkey6] as nvarchar(4) ) + ', ' +
cast( [fkey7] as nvarchar(4) ) + ', ' +
cast( [fkey8] as nvarchar(4) ) + ', ' +
cast( [fkey9] as nvarchar(4) ) + ', ' +
cast( [fkey10] as nvarchar(4) ) + ', ' +
cast( [fkey11] as nvarchar(4) ) + ', ' +
cast( [fkey12] as nvarchar(4) ) + ', ' +
cast( [fkey13] as nvarchar(4) ) + ', ' +
cast( [fkey14] as nvarchar(4) ) + ', ' +
cast( [fkey15] as nvarchar(4) ) + ', ' +
cast( [fkey16] as nvarchar(4) )
), '' )
,rkeyid
,cast( [rkey1] as nvarchar(4) ) + ', ' +
cast( [rkey2] as nvarchar(4) ) + ', ' +
cast( [rkey3] as nvarchar(4) ) + ', ' +
cast( [rkey4] as nvarchar(4) ) + ', ' +
cast( [rkey5] as nvarchar(4) ) + ', ' +
cast( [rkey6] as nvarchar(4) ) + ', ' +
cast( [rkey7] as nvarchar(4) ) + ', ' +
cast( [rkey8] as nvarchar(4) ) + ', ' +
cast( [rkey9] as nvarchar(4) ) + ', ' +
cast( [rkey10] as nvarchar(4) ) + ', ' +
cast( [rkey11] as nvarchar(4) ) + ', ' +
cast( [rkey12] as nvarchar(4) ) + ', ' +
cast( [rkey13] as nvarchar(4) ) + ', ' +
cast( [rkey14] as nvarchar(4) ) + ', ' +
cast( [rkey15] as nvarchar(4) ) + ', ' +
cast( [rkey16] as nvarchar(4) )
from sysreferences
right join #tables
on tabid = fkeyid
order by tablevel
--====================================================================================
--load sample tables
select @l = 0
select @maxlevel = max(tablevel) from #tables
Select @rowid = 1
Select @alias = 66
while @rowid <= ( select count(*) from #t ) begin
--set variables
Select @tab = object_name( fkid )
,@rtab = object_name( rkid )
,@fcolstr = fkcolid
,@rcolstr = rkcolid
From #t
where rowid = @rowid
/* if @tab <> 'employees' begin
Select @rowid = @rowid +1
continue
End
*/
Select @requiredrows = ceiling( (@percentage / 100.00) * rowcnt )
,@tabowner = tabowner
,@tablevel = tablevel
From #tables
Where tabname = @tab
Select @join = isnull( @join, '' ) + char(10) +
'inner join [sampledb].[' +@tabowner +'].[' + @rtab + '] as ' + Char(@alias)
,@jc = isnull( @jc-2, '' ) + char(10) + 'on' +char(9)
--first reference columns
Select @fcolid = left( @fcolstr, charindex( ',', @fcolstr ) - 1 )
Select @rcolid = left( @rcolstr, charindex( ',', @rcolstr ) - 1 )
While @fcolid <> 0 begin
--shorten column string
Select @fcolstr = substring( @fcolstr,
charindex( ',', @fcolstr ) + 1,
len( @fcolstr )
)
Select @rcolstr = substring( @rcolstr,
charindex( ',', @rcolstr ) + 1,
len( @rcolstr )
)
--find reference column names
Select @rcol = name
from syscolumns
where id = object_id( @rtab )
And colid = @rcolid
Select @fcol = name
from syscolumns
where id = object_id( @tab )
And colid = @fcolid
select @jc-2 = @jc-2 + '[A].['+ @fcol +'] = '
Select @jc-2 = @jc-2 + '[' +Char( @alias)+ '].['+ @rcol +'] '+ char(10) +'And'+char(9)
Select @fcolid = left( @fcolstr, charindex( ',', @fcolstr ) - 1 )
Select @rcolid = left( @rcolstr, charindex( ',', @rcolstr ) - 1 )
End
Select @jc-2 = case
When len( @jc-2 ) > 5 then left( @jc-2, len( @jc-2 ) - 5 )
Else @jc-2
End
Select @join = @join + @jc-2
Select @jc-2 = ''
Select @rowid = @rowid + 1
,@alias = @alias + 1
If @tab <>
( select object_name( fkid )
From #t
where rowid = @rowid
) Begin
select @collist = '', @RunSQL =''
select @collist = @collist + '[A].['+ name +'], '+ char(10)+ char(9)
From syscolumns
Where id = object_id( @tab )
order by colid
set @collist = left( @collist, len( @collist)- 2)
if @tab = @rtab begin
print @tab +' is a self referencing table.'
set @RunSQL =
'set rowcount ' + cast( @requiredrows as varchar(9) )+ char(10) +
'Begin ' + char(10) +
'insert into [sampledb].[' +@tabowner +'].['+ @tab +']' + char( 10 ) +
'( '+ left( @collist, len( @collist )-1) +')' + char(10) +
'Select ' + char(10) +
char(9) + left( @collist, len( @collist )-1) +char(10) +
'From [' + @tab + '] as A' + char(10)+
'Where '+ @fcol+ ' is null ' + char(10) +
'End'+ char(10) + char(10) +
'Begin ' + char(10) +
'insert into [sampledb].[' +@tabowner +'].['+ @tab +'] (' + char( 10 ) +
left( @collist, len( @collist )-1) +')' + char(10) +
'Select ' + char(10) +
char(9) + left( @collist, len( @collist )-1) +char(10) +
'From [' + @tab + '] as A' + char(10)+
@join + char(10) +
'End'+ char(10) + char(10) +
'Set rowcount 0'+
char(10) + char(10)
End
Else Begin
Set @RunSQL =
'set rowcount ' + cast( @requiredrows as varchar(9) )+ char(10) +
'insert into [sampledb].[' +@tabowner +'].['+ @tab +']' + char( 10 ) +
'( '+ left( @collist, len( @collist )-1) +')' + char(10) +
'Select ' + char(10) +
char(9) + left( @collist, len( @collist )-1) +char(10) +
'From [' + @tab + '] as A' +
@join + char(10) +
'Set rowcount 0'+
char(10) + char(10)
End
If @tablevel = 1 begin
Set @RunSQL =
'set rowcount ' + cast( @requiredrows as varchar(9) )+ char(10) +
'insert into [sampledb].[' +@tabowner +'].['+ @tab +']' + char( 10 ) +
'( '+ left( @collist, len( @collist )-1) +')' + char(10) +
'Select ' + char(10) +
char(9) + left( @collist, len( @collist )-1) +char(10) +
'From [' + @tab + '] as A' + char(10)+
'Set rowcount 0'+
char(10) + char(10)
End
If exists(
Select * from syscolumns
where id = object_id( @tab )
And status = 0x80
)Begin
Select @RunSQL =
'set identity_insert [SampleDB].['+ @tabowner +'].['+ @tab +'] ON' +
char( 10 ) + left( @RunSQL, len( @RunSQL )- 1 ) +
'set identity_insert [SampleDB].['+ @tabowner +'].['+ @tab +'] Off' +
char( 10 )
+'Select * from [SampleDB].['+ @tabowner +'].['+ @tab +'] '+char( 10 )
End
-- Print( @RunSQL )
Exec( @RunSQL )
if @@error = 0
print @tab +' transferred '+ cast( @@rowcount as varchar(9) ) + ' row(s) successfully.' +char(10)
Select @join = ''
,@jc = ''
,@Alias = 66
End
End
--====================================================================================
--select * from #tables order by tablevel, tabname
--select object_name(fkid),* from #t
--drop table #tables
February 7, 2003 at 1:19 am
I have not looked at this script in a few weeks so, of the top of my head,
You will need to create Sampledb.
Also script all the tables, with indexes, and relationships.
Go to the source database and run the script from there in the QA. as mentioned before this has not been perfected and you may come up with a problem that I have not encountered yet. Let me know if you need any more info.
Cheers...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply