Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>