Copying tables from one database to another, and me…

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