Moving large amount of data

I have a stored procedure that we've been using here for many months
that allows us to migrate data from a production databse to test and
dev so we always have fresh (and real) data to play with. I couldn't
just backup/restore as I may have added new stored procs or new
columns to the target tables and I don't want these overwritten. The
solution was to create a data-driven stored proc that would iterate
all the tables that the source and target have in common. Then, for
each table, it would iterate all the columns that are in common to
create a dynamic INSERT INTO...SELECT FROM statement that would list
all the common fields.

My question is how to handle very large tables. INSERT INTO...SELECT
FROM works isn't always the best approach when dealing with tables
that have row counts in the tens of millions and greater. These often
take hours to move.

Given what we're trying to do here, Does anyone have a better
suggestion as to how to handle this dynamically?

If anyone would like me to post the stord proc (it works quite well)
I'll be happy to do so.

Thanks

Carl

On Jan 8, 11:09 am, "Michael Coles" wrote:
> Meant to add a couple of references:http://msdn.microsoft.com/en-us/library/ms188029.aspxhttp://msdn.microsoft.com/en-us/library/ms191244.aspx
>
> --
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "SetonSoftware" wrote in message
>
>
>
>
>
> >I have a stored procedure that we've been using here for many months
> > that allows us to migrate data from a production databse to test and
> > dev so we always have fresh (and real) data to play with. I couldn't
> > just backup/restore as I may have added new stored procs or new
> > columns to the target tables and I don't want these overwritten. The
> > solution was to create a data-driven stored proc that would iterate
> > all the tables that the source and target have in common. Then, for
> > each table, it would iterate all the columns that are in common to
> > create a dynamic INSERT INTO...SELECT FROM statement that would list
> > all the common fields.
>
> > My question is how to handle very large tables. INSERT INTO...SELECT
> > FROM works isn't always the best approach when dealing with tables
> > that have row counts in the tens of millions and greater. These often
> > take hours to move.
>
> > Given what we're trying to do here, Does anyone have a better
> > suggestion as to how to handle this dynamically?
>
> > If anyone would like me to post the stord proc (it works quite well)
> > I'll be happy to do so.
>
> > Thanks
>
> > Carl- Hide quoted text -
>
> - Show quoted text -

Michael

Many thanks but the problem is that SELECT ... INTO creates a new
table. I need to populate data in existing tables without losing any
constraints, primary key settings, indices, etc. I want to move the
production data to dev/test and ONLY the data. The existing structures
on the target must remain as is.

Thanks

Carl