Inserting Updating and Deleting large numbers of records

11 Jun 2009 2:47 PM

Tim Marsden

Hi,
Imagine I have a grid 1000 rows x 30 columns. The rows represent Products,
the columns Dates. Order amounts are placed in the corresponding cells of
the grid. I want to update a SQL Table with all combinations of Product /
Date with the Order amount.

If the Product / Date combination exists in the Table, I want to update the
Order amount with the value entered in the grid.
If the Product / Date combination does not exist I want to insert the
Product / Date / Amount into the Table.

If the Order amount is 0, I want to delete the record from the Table if it
exists.
This could potentially mean executing a huge amount of UPDATE, INSERT and
DELETE statements.

What is the most efficient way of doing this?

All ideas and suggests are very much appreciated.

--
Tim Marsden

11 Jun 2009 3:25 PM

John Bell

Show quote Hide quote

"Tim Marsden" <tmqsl@newsgroup.nospam> wrote in message
news:AD900197-13CE-4FC2-9ACC-410F098E0CF7@microsoft.com...
> Hi,
> Imagine I have a grid 1000 rows x 30 columns. The rows represent Products,
> the columns Dates. Order amounts are placed in the corresponding cells of
> the grid. I want to update a SQL Table with all combinations of Product /
> Date with the Order amount.
>
> If the Product / Date combination exists in the Table, I want to update
> the
> Order amount with the value entered in the grid.
> If the Product / Date combination does not exist I want to insert the
> Product / Date / Amount into the Table.
>
> If the Order amount is 0, I want to delete the record from the Table if it
> exists.
> This could potentially mean executing a huge amount of UPDATE, INSERT and
> DELETE statements.
>
> What is the most efficient way of doing this?
>
> All ideas and suggests are very much appreciated.
>
> --
> Tim Marsden

Hi Tim

1000 rows is not a large amount of changes!!

If you update, insert, then delete you won't be doing too many updates. If
you are using SQL 2008 you could look at table-valued parameters and the
MERGE statement.

John

11 Jun 2009 3:36 PM

Tim Marsden

John, many thanks for your reply.

Heres a little more clarification.

I loop through the rows and columns, if the cell is 0 I run a DELETE, else,
run a UPDATE statement, if this returns 0 rows updated, run a INSERT
statement.

Is this an acceptible method?

--
Tim Marsden

Show quoteHide quote

"John Bell" wrote:

>
> "Tim Marsden" <tmqsl@newsgroup.nospam> wrote in message
> news:AD900197-13CE-4FC2-9ACC-410F098E0CF7@microsoft.com...
> > Hi,
> > Imagine I have a grid 1000 rows x 30 columns. The rows represent Products,
> > the columns Dates. Order amounts are placed in the corresponding cells of
> > the grid. I want to update a SQL Table with all combinations of Product /
> > Date with the Order amount.
> >
> > If the Product / Date combination exists in the Table, I want to update
> > the
> > Order amount with the value entered in the grid.
> > If the Product / Date combination does not exist I want to insert the
> > Product / Date / Amount into the Table.
> >
> > If the Order amount is 0, I want to delete the record from the Table if it
> > exists.
> > This could potentially mean executing a huge amount of UPDATE, INSERT and
> > DELETE statements.
> >
> > What is the most efficient way of doing this?
> >
> > All ideas and suggests are very much appreciated.
> >
> > --
> > Tim Marsden
>
> Hi Tim
>
> 1000 rows is not a large amount of changes!!
>
> If you update, insert, then delete you won't be doing too many updates. If
> you are using SQL 2008 you could look at table-valued parameters and the
> MERGE statement.
>
> John
>

11 Jun 2009 7:48 PM

John Bell

Show quote Hide quote

"Tim Marsden" <tmqsl@newsgroup.nospam> wrote in message
news:9D83472A-FBCC-4BA1-94FD-D6C1102A41EF@microsoft.com...
> John, many thanks for your reply.
>
> Heres a little more clarification.
>
> I loop through the rows and columns, if the cell is 0 I run a DELETE,
> else,
> run a UPDATE statement, if this returns 0 rows updated, run a INSERT
> statement.
>
> Is this an acceptible method?
>
>
>
> --
> Tim Marsden
>

Hi Tim

I am not sure what these rows and columns are relating to!!! If you are not
running SQL 2008 to use a TVP, then you could convert the whole table to XML
and pass that to a stored procedure providing that it is no bigger than
2,147,483,647 bytes. Using a TVP or OPENXML you could then have a single
UPDATE, INSERT and DELETE (or one MERGE) statement matching on an
identifying value (columns) rather than doing it row by row. If DELETEs are
by exception, then they don't need to be included in the data that you pass.

John