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

Baby Toys, Potty Words, SQL, and Me...

So little Liam just had his first birthday last Thursday.  Among the plethora of toys dumped on him is an incredibly annoying, er, wonderful alphabet speaking caterpillar:

There’s a small yellow bow switch just below the head with three settings, plus off (dear God): letter pronouncing, alternate letter pronouncing, and color word.  These obviously coincide with the feet, so if you press the A foot, the caterpillar will speak “A” on the letter pronunciation, “ah” on the alternate pronunciation, and “red” on the color one.

I had the setting on alternate pronunciation, and was lying back letting Liam play and climb on me.  While smashing the caterpillar, he hit a bunch of keys, seemingly at once.  The F key said “fuh” first, then the caterpillar giggled and said “that tickles!”, and then “kh” for the K key.

This immediately caught my attention, and I had to make the evil caterpillar curse violently so mom would toss it in the closet.  With Liam’s full attention, I hit the F, then K keys.  Again, “he he he, that tickles!” between the two keys.  I took the caterpillar from Liam.  This was now science!  I tried K, O, “he he he, that tickles!”, K.  FASCINATING!  They built in anti-potty-wording!  It blocked T-I-“he he he, that tickles!”-T, F then C, K-O-“he he he, that tickles!”-C, C-O-“he he he, that tickles!”-C, C-U-“he he he, that tickles!”-M, and P-I-S (latin for to pee).  J-I-Z worked, as well as B-U-T.  The word for buttocks or donkey (A-“he he he, that tickles!”-S) did not.

This makes me wonder about other alphabet toys and whether they were as thoroughly scrutinized or not.  Needless to say, I had been working on some SQL for work and decided to see how well it tells the SOUNDEX difference between various spellings of rather naughty words using DIFFERENCE.  Using SQL 2005, the results were startlingly poor.  In a few instances, SQL was smart in its comparisons, however in many cases a comparison between, say, a naughty reference to a woman’s genitals and a man’s returns a DIFFERENCE value of 3.  For those who don’t know, the value returned ranges from 0 to 4, 0 being completely dissimilar to 4 being extremely similar if not identical.  The values in the above case should have been a 0.  The SQL and source for the simple tests are here.  Basically, I’d at least look at additional resources for filtering potty language from user input.

I guess really the only useful thing that came out of this arguably utterly useless exercise is some quick C# I threw together to de-Cartesian-ize my SQL result set:

using System;

using System.Collections.Generic;

using System.Text;

 

using System.IO;

 

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

            List<string> csv = new List<string>(File.ReadAllLines("potty.csv"));

            List<string> csvmod = new List<string>();

            csv.FindAll(delegate(string s)

            {

                string[] l = s.Split(new char[] { ',' });

                if (csvmod.FindIndex(delegate(string s2)

                {

                    return (s2.Contains(l[0] + ",") && s2.Contains(l[1] + ",") && (s2.IndexOf(l[0] + ",") != s2.IndexOf(l[1] + ",")));

                }) < 0)

                    csvmod.Add(s);

                return false;

            });

            File.WriteAllLines("potty-less.csv", csvmod.ToArray());

        }

    }

}

Just an example of hot anonymous delegate action for ya.

Tasty…

~zagnut

SQL Server 2005 Database Truncations and Me

Hi kids...

Here's a hot action script for truncating all the tables in your SQL 2005 db for you.  Please post your comments, changes, etc. and I'll update this throughout:

select distinct
    t.table_schema + '.' + t.table_name as [table]
    , case when tc.constraint_type = 'FOREIGN KEY' then 1 else 2 end as [order]
into #master_tbl
from information_schema.tables t
inner join information_schema.table_constraints tc on
    tc.table_schema = t.table_schema and
    tc.table_name = t.table_name
where t.table_type = 'BASE TABLE'
and tc.constraint_type like '% KEY'
order by 2

alter table #master_tbl
add idx bigint identity(1,1)

select *
into #k
from #master_tbl

select *
into #rk
from #k

while ((select count(*) from #k) > 0)
begin

    declare @table varchar(1024)
    declare @idx bigint

    select top 1
        @table = [table]
        , @idx = idx
    from #k

    exec ('alter table ' + @table + ' nocheck constraint all')

    delete from #k where idx = @idx

end

drop table #k


select *
into #t
from #master_tbl

while ((select count(*) from #t) > 0)
begin

    declare @tidx bigint
    declare @ttable varchar(1024)

    select top 1
        @tidx = idx
        , @ttable = [table]
    from #t
    order by [order]

    begin try
        exec('truncate table ' + @ttable)
        print @ttable + ' purged'
    end try
    begin catch
        print @ttable + ' could not be purged.'
    end catch

    delete from #t where idx = @tidx

end

drop table #t

while ((select count(*) from #rk) > 0)
begin

    declare @ktable varchar(1024)
    declare @kidx bigint

    select top 1
        @ktable = [table]
        , @kidx = idx
    from #rk

    exec ('alter table ' + @ktable + ' with check check constraint all')

    delete from #rk where idx = @kidx

end

drop table #rk

drop table #master_tbl

Cheers...