So, lately I had the dilemma of having done a bunch of preliminary SQL work in 2008 Express, then needing to port my results over to a staging SQL 2005 server at work. I casually backed up the 2008 database and lo and behold, couldn’t restore it! SSIS wasn’t set up, and I had to gets stuff done ASAP! I searched on Google, and all I got were either “use SSIS” or “it can’t be done.” In other words, those who’ve done this before haven’t shared with the rest of us.
So a proc I wrote came in handy…
I threw together this stored procedure, linked to the remote 2005 server, and used this proc to move all my data. It’s very simple and WYSIWYG-ish right now, as it:
- doesn’t copy over indexes
- doesn’t copy over table schemas
- just copies the tables into a new database with the same table names and data
A Quick Sample Run-Through
So, first let’s create a database named SimpleSample. We’ll create two tables, one with prime numbers and one with the beginning of the Fibonacci sequence.
For our primes:
useSimpleSample
go
declare@table table
(
primesint
)
declare@counter int
set@counter = 1
while (@counter< 1000)
begin
insert into @table(primes) values(@counter)
set @counter =@counter + 1
end
set@counter = 2
while ((selectcount(1) from @table where primes >@counter) >0)
begin
delete from @table
where (((primes % @counter) = 0) and (primes > @counter))
select top 1 @counter= primes from@table where primes >@counter order byprimes
end
select*
intoPrimes
from@table
Now for Fibonacci:
useSimpleSample
go
setnocount on
declare@fibo table
(
naccibigint
)
while (((selectmax(nacci) from @fibo) < 1000) or ((select COUNT(1) from @fibo) = 0))
begin
if ((select count(1) from @fibo) > 1)
begin
insert into @fibo(nacci)
select sum(i) as nacci from (
select top 2 nacci as i from @fibo order by nacci desc
) tops
end
else
insert into @fibo(nacci) values(1)
end
select*
intoFibonacci
from @fibo
Now that we have two sample tables we’re going to copy, create a second database named SimpleSample2. Our routine will be simple: get a list of the names of our source tables, and for each name in the list, SELECT from the table with that name in our source database INTO a new table with that same name in our destination database.
There are many ways to get the list of tables. I ended up using the sys.sp_tables proc:
declare@temp table
(
TABLE_QUALIFIERnvarchar(200)
, TABLE_OWNER nvarchar(200)
, TABLE_NAME nvarchar(200)
, TABLE_TYPE nvarchar(200)
, REMARKS nvarchar(max)
)
begin
declare@sourceDatabase varchar(100)
set@sourceDatabase = 'SimpleSample'
declare@spTables varchar(1000)
set@spTables = 'exec '+ @sourceDatabase +'.sys.sp_tables'
insertinto @temp
execsp_sqlexec @spTables
select* from @temp
end
After that, I cleaned out the data to just dbo created tables:
delete
from@temp
whereTABLE_TYPE <> 'TABLE'
declare@ourTables table
(
idxint identity(1,1)
, name nvarchar(200)
)
insertinto @ourTables
select TABLE_NAME from @temp
Finally, throw those tables into the destination database:
declare@name nvarchar(200)
declare@query varchar(2000)
declare@idx int
while ((selectCOUNT(1) from @ourTables) > 0)
begin
select top 1 @idx = idx, @name = name from@ourTables
set @query = 'SELECT * INTO ' +@toDb + '.dbo.['+ @name + '] FROM ' + @name
exec sp_sqlexec @query
delete from@ourTables where idx =@idx
end
The SQL sources are here. I’m sure it can be spruced up to do many more great things, such as those I initially listed. If you decide to add anything, please post in the comments or try to get your additions / changes to me and I’ll update the SQL. You’ll even have the extra bonus of being credited on this immensely popular blog!
~ZagNut
