"Guy Hocking" <guy.hocking@bradflack.com> wrote in message
news:O6KLUlnsDHA.2244@TK2MSFTNGP09.phx.gbl...
> Hi there,
>
> I am new to SQL Server, we are running SQL Server 7
> I am having some trouble with creating a delete query.
>
> Basically, i have developed a Access database that exports data to a .csv
> file, to be imported to SQL server via a scheduled DTS package.
> Most of the .csv files will only have new data to ammend to the SQL tables
> which is fine doing the above.
> But some of the .csv files have the entire data dump of the Access table
and
> needs to imported over the current data in the SQL table.
>
> What i was thinking was to make a Query/View to delete the data in the
> relevant table and then call the DTS package.
> I have no idea how to do this - View? Stored Procedure?
> But ideally it would delete the data and then call the DTS package, all in
> one function......
>
> Also, would it then be possible to schedule/automate this service?
>
> Thanks for any help
>
> Regards
>
> Guy
>
>

>Hi there,
>
>I am new to SQL Server, we are running SQL Server 7
>I am having some trouble with creating a delete query.
>
>Basically, i have developed a Access database that exports data to a .csv
>file, to be imported to SQL server via a scheduled DTS package.
>Most of the .csv files will only have new data to ammend to the SQL tables
>which is fine doing the above.
>But some of the .csv files have the entire data dump of the Access table and
>needs to imported over the current data in the SQL table.
>
>What i was thinking was to make a Query/View to delete the data in the
>relevant table and then call the DTS package.
>I have no idea how to do this - View? Stored Procedure?
>But ideally it would delete the data and then call the DTS package, all in
>one function......
>
>Also, would it then be possible to schedule/automate this service?
>
>Thanks for any help
>
>Regards
>
>Guy
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:Or4GN9nsDHA.536@tk2msftngp13.phx.gbl...
> OK
>
> Reimporting all the data is fine if you have
>
> A. No constraints reliant on the data
> B. Your quantity of data is not too large
>
> Personally I would do things like this
>
> 1. Have a working table in a staging area to hold each CSV.
> 2. Import each CSV into their respective staging table
> 3. Use TSQL statements to compare the working table data with that is in
> your destination tables. By comparing Key values you can determine
whether
> the Data in the real tables requires
>
> 1. Adding
> 2. Updating
> 3. Deleteing.
>
> As for scheduling then yes you can do this using SQL Server Agent and a
Job.
> Be careful to read this though
>
> http://support.microsoft.com/?kbid=269074
>
>
>
>
> --
>
> ----------------------------
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
>
> "Guy Hocking" <guy.hocking@bradflack.com> wrote in message
> news:O6KLUlnsDHA.2244@TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > I am new to SQL Server, we are running SQL Server 7
> > I am having some trouble with creating a delete query.
> >
> > Basically, i have developed a Access database that exports data to a
..csv
> > file, to be imported to SQL server via a scheduled DTS package.
> > Most of the .csv files will only have new data to ammend to the SQL
tables
> > which is fine doing the above.
> > But some of the .csv files have the entire data dump of the Access table
> and
> > needs to imported over the current data in the SQL table.
> >
> > What i was thinking was to make a Query/View to delete the data in the
> > relevant table and then call the DTS package.
> > I have no idea how to do this - View? Stored Procedure?
> > But ideally it would delete the data and then call the DTS package, all
in
> > one function......
> >
> > Also, would it then be possible to schedule/automate this service?
> >
> > Thanks for any help
> >
> > Regards
> >
> > Guy
> >
> >
>
>

> In article <O6KLUlnsDHA.2244@TK2MSFTNGP09.phx.gbl>, Guy Hocking
> <guy.hocking@bradflack.com> writes
> >Hi there,
> >
> >I am new to SQL Server, we are running SQL Server 7
> >I am having some trouble with creating a delete query.
> >
> >Basically, i have developed a Access database that exports data to a .csv
> >file, to be imported to SQL server via a scheduled DTS package.
> >Most of the .csv files will only have new data to ammend to the SQL
tables
> >which is fine doing the above.
> >But some of the .csv files have the entire data dump of the Access table
and
> >needs to imported over the current data in the SQL table.
> >
> >What i was thinking was to make a Query/View to delete the data in the
> >relevant table and then call the DTS package.
> >I have no idea how to do this - View? Stored Procedure?
> >But ideally it would delete the data and then call the DTS package, all
in
> >one function......
> >
> >Also, would it then be possible to schedule/automate this service?
> >
> >Thanks for any help
> >
> >Regards
> >
> >Guy
> >
>
> You can use the Execute SQL Task to run a Delete statement on your
> table. Just link this with workflow to run before your the export
> (DataPump) task. You will need to open the package manually to add this,
> if still using the Wizard only.
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
>
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>

"Guy Hocking" <guy.hocking@bradflack.com> wrote in message
news:O4KsyCosDHA.2208@TK2MSFTNGP10.phx.gbl...
> Thanks for that Allan,
>
> I dont think the amount of data isnt too much (how much is too much?), and
i
> would rather to all the guinea-pig work in Access. The data is currently
> exported into another table in Access with the same naming convention and
> design as its SQL equivalent, then the data is exporeted to CSV, once done
> the data is tagged in access so it is not exported again.
>
> However, some of the tables will be very different as the data will change
> frequently, it seems alot easier to re-import as its only 30-40k of
records,
> nothing monsterous and it doesnt take an age as iv tested it.
>
> This difficulty is i have no idea where to write the query? view, stored
> procedure, i really dont know..........i cant seem to create a "delete"
> function view, it only seems to do the SELECT function.
>
> I am very new to SQL, so i apoligise for my ignorance
>
> Guy
>
>
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:Or4GN9nsDHA.536@tk2msftngp13.phx.gbl...
> > OK
> >
> > Reimporting all the data is fine if you have
> >
> > A. No constraints reliant on the data
> > B. Your quantity of data is not too large
> >
> > Personally I would do things like this
> >
> > 1. Have a working table in a staging area to hold each CSV.
> > 2. Import each CSV into their respective staging table
> > 3. Use TSQL statements to compare the working table data with that is
in
> > your destination tables. By comparing Key values you can determine
> whether
> > the Data in the real tables requires
> >
> > 1. Adding
> > 2. Updating
> > 3. Deleteing.
> >
> > As for scheduling then yes you can do this using SQL Server Agent and a
> Job.
> > Be careful to read this though
> >
> > http://support.microsoft.com/?kbid=269074
> >
> >
> >
> >
> > --
> >
> > ----------------------------
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> > "Guy Hocking" <guy.hocking@bradflack.com> wrote in message
> > news:O6KLUlnsDHA.2244@TK2MSFTNGP09.phx.gbl...
> > > Hi there,
> > >
> > > I am new to SQL Server, we are running SQL Server 7
> > > I am having some trouble with creating a delete query.
> > >
> > > Basically, i have developed a Access database that exports data to a
> .csv
> > > file, to be imported to SQL server via a scheduled DTS package.
> > > Most of the .csv files will only have new data to ammend to the SQL
> tables
> > > which is fine doing the above.
> > > But some of the .csv files have the entire data dump of the Access
table
> > and
> > > needs to imported over the current data in the SQL table.
> > >
> > > What i was thinking was to make a Query/View to delete the data in the
> > > relevant table and then call the DTS package.
> > > I have no idea how to do this - View? Stored Procedure?
> > > But ideally it would delete the data and then call the DTS package,
all
> in
> > > one function......
> > >
> > > Also, would it then be possible to schedule/automate this service?
> > >
> > > Thanks for any help
> > >
> > > Regards
> > >
> > > Guy
> > >
> > >
> >
> >
>
>