Clear data out of multiple tables.

I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
Thanks!
Vic(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences|||Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
Thanks,|||sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.|||Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.|||In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.

Hope that's of some help. Feel free to ask away!|||I just went back and re-read my original post and I see where I need to clarify a few points.

I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.

Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better! :o

So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.

Thanks in advance for helping.|||to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:

http://www.elsasoft.org/tools.htm

it will also script all the data out (optionally) using bcp.exe.

I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.

check whats needed if load doubled over the next few months

If we anticipate our load to double i.e twice the number of customers,etc..,
how do we go about planning if the db system today is adequate to meet that
demand ? What areas should we look at and what to consider to provide that
scale ?
ThanksOn Jan 31, 8:14=A0am, "Hassan" <has...@.test.com> wrote:
> If we anticipate our load to double i.e twice the number of customers,etc.=.,
> how do we go about planning if the db system today is adequate to meet tha=t
> demand ? What areas should we look at and what to consider to provide that=
> scale ?
> Thanks
You will need to start analyzing towards the capacity planning and
the elements you will have to think for
Disk Space
CPU (32 bit or 64 bit as per your need)
Memory :- based on CPU selection again you can think for Memory.
Thanks
Ajay|||Before you can answer that question, you need to determine where your
bottleneck will be on your database server when your number of customers
doubles.
Linchi
"Hassan" wrote:
> If we anticipate our load to double i.e twice the number of customers,etc..,
> how do we go about planning if the db system today is adequate to meet that
> demand ? What areas should we look at and what to consider to provide that
> scale ?
> Thanks
>|||Unfortunately most things don't scale linearly either, in addition to the
other comments on this thread. Once you hit saturation points for various
resources, performance starts to degrade dramatically, sometimes at at
exponentially decreasing rate. You cannot specifically know WHERE the
limits are either, although a good analysis can get you pretty close
usually.
I will once again take this opportunity to recommend you hire a seasoned DBA
sooner rather than later. :)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Hassan" <hassan@.test.com> wrote in message
news:%23B9Rbd7YIHA.4448@.TK2MSFTNGP03.phx.gbl...
> If we anticipate our load to double i.e twice the number of
> customers,etc.., how do we go about planning if the db system today is
> adequate to meet that demand ? What areas should we look at and what to
> consider to provide that scale ?
> Thanks