Question on automatic copying table from one datbase to another on same SQL Serv

Currently have two databases. A live database and a history database. Also have the ability to purge data from the live to the history database. What I'm looking for is the ability, if the table doesn't exist in history, to automatically create the table in the history database using the format of the table within the live database. Any help would be greatly appreciated. ThanksLog Shipping sounds like a plan

[BOL] Log shipping|||Originally posted by Ruprect
Log Shipping sounds like a plan

[BOL] Log shipping Within the same server? Kinda overkill, isn't it?|||live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option?|||Originally posted by Ruprect
live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option?

I figure if I have to an option would be to use the sys tables for the information and build the create table string on the fly but was hoping there was a quicker and easier way ^_^|||Originally posted by Ruprect
live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option? Hmmm, get a real job? OK, that was a joke ;)

Trx Replication? I don't know, still sounds too heavy. I'd drop the idea of having "live history" database residing on the same server. I'd worry about that one once a day, at midnight, after all backups are done.|||How about INSERT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp) if the table exists, or SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) if the target table does not exist?

-PatP|||i was gonna go with dts...
a scheduled import export but this could be handled with a vbscript looking for the existence of the table and running one of two possible sub packages depending on the table's design
the only problem is the fact that this doesnot rely on the actual table's schema. you would have to know that already.

in the case of the actual question, i think that pat has hit on the easiest solution.|||After you do the insert into, won't you still need to create the matching indexes and relationships, or are you not worried about that.|||I would second Ruprect suggestion about using Log shipping and say using your own log shipping you can achieve the task.