Inserts gradually slow down

I'm inserting 2 million+ records from a C# routine which starts out
very fast and gradually slows down. Each insert is through a stored
procedure with no transactions involved.

If I stop and restart the process it immediately speeds up and then
gradually slows down again. But closing and re-opening the connection
every 10000 records didn't help.

Stopping and restarting the process is obviously clearing up some
resource on SQL Server (or DTC??), but what? How can I clean up that
resource manually?BTW, the tables have no indexes or constraints. Just simple tables
that are dropped and recreated each time the process is run.|||(andrewbb@.gmail.com) writes:
> I'm inserting 2 million+ records from a C# routine which starts out
> very fast and gradually slows down. Each insert is through a stored
> procedure with no transactions involved.
> If I stop and restart the process it immediately speeds up and then
> gradually slows down again. But closing and re-opening the connection
> every 10000 records didn't help.
> Stopping and restarting the process is obviously clearing up some
> resource on SQL Server (or DTC??), but what? How can I clean up that
> resource manually?

If I understand thius correctly, every time you restart the process
you also drop the tables and recreate them. So that is the "resource"
you clear up.

One reason could be autogrow. It might be an idea to extend the database
to reasonable size before you start loading. If you are running with
full recovery, this also includes the transaction log.

You could also consider adding a clustered index that is aligned with
the data that you insert. That is, you insert the data in foo-order,
you should have a clustered index on foo.

But since 2 million rows is quite a lot, you should probably examine
more efficient methods to load them. The fastest method is bulk-load,
but ADO .Net 1.1 does not have a bulk-load interface. But you could
run command-line BCP.

You could also build XML strings with your data and unpack these
with OPENXML in the stored procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In my experience 9 times out of 10 the reason for the problem is a file
that keeps growing by 10%.
either pre-allocate a big file as Erland suggests or change the
filegrowth from 10% (the default, which by the way is a bad default) to
something in the region of 20 mb or so.|||You're right, I removed the drop and re-create and it's definitely
slower when data already exists. So how would you suggest loading this
data?

The text file contains 11 different types of Rows. Each type of row
goes to a separate table, so I need to read each line, determine its
type, parse it and insert into the appropriate table.

Can BCP handle this? DTS? Or your XML idea?

Thanks

Erland Sommarskog wrote:
> (andrewbb@.gmail.com) writes:
> > I'm inserting 2 million+ records from a C# routine which starts out
> > very fast and gradually slows down. Each insert is through a
stored
> > procedure with no transactions involved.
> > If I stop and restart the process it immediately speeds up and then
> > gradually slows down again. But closing and re-opening the
connection
> > every 10000 records didn't help.
> > Stopping and restarting the process is obviously clearing up some
> > resource on SQL Server (or DTC??), but what? How can I clean up
that
> > resource manually?
> If I understand thius correctly, every time you restart the process
> you also drop the tables and recreate them. So that is the "resource"
> you clear up.
> One reason could be autogrow. It might be an idea to extend the
database
> to reasonable size before you start loading. If you are running with
> full recovery, this also includes the transaction log.
> You could also consider adding a clustered index that is aligned with
> the data that you insert. That is, you insert the data in foo-order,
> you should have a clustered index on foo.
> But since 2 million rows is quite a lot, you should probably examine
> more efficient methods to load them. The fastest method is bulk-load,
> but ADO .Net 1.1 does not have a bulk-load interface. But you could
> run command-line BCP.
> You could also build XML strings with your data and unpack these
> with OPENXML in the stored procedure.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried adjusting the settings... both different %'s and specific MBs,
but the slow down is the same in all cases.

It's dramatically slower to insert records into a 100,000 row table
than an empty one I guess.|||
andrewbb@.gmail.com wrote:

> I tried adjusting the settings... both different %'s and specific MBs,
> but the slow down is the same in all cases.
> It's dramatically slower to insert records into a 100,000 row table
> than an empty one I guess.

So these are existing tables? Do these tables have indexes already? If you
had a clustered index, for instance, and were inserting data in a random
order, there would be a lot of inefficiency and index maintenance. The
fastest way to get that much data in, would be to sort it by table,
and in the order you'd want the index, and BCP it in, to tables with no
indices, and then create your indexes.|||(andrewbb@.gmail.com) writes:
> You're right, I removed the drop and re-create and it's definitely
> slower when data already exists. So how would you suggest loading this
> data?

I can't really give good suggestions about data that I don't anything
about.

> The text file contains 11 different types of Rows. Each type of row
> goes to a separate table, so I need to read each line, determine its
> type, parse it and insert into the appropriate table.
> Can BCP handle this? DTS? Or your XML idea?

If the data has a conformant appearance, you could load the lot in a staging
table and then distribute the data from there.

You could also just write new files for each table and then bulk-load
these tables.

It's possible that a Data Pump task in DTS could do all this out
of the box, but I don't know DTS.

The XML idea would require you parse the file, and build an XML document
of it. You wouldn't have to build 11 XML documents, though. (Although
that might be easier than building one big one.)

It's also possible to bulk-load from variables, but not in C# with
ADO .Net 1.1.

So how big did you make the database before you started loading? With
two million records, you should have at least 100 MB for both data and
log.

By the way, how do call the stored procedure? You are using
CommandType.StoredProcedure, aren't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the help. I found the fastest way to do it in a single SP:

- BULK INSERT all data into a 2 column staging table using a BCP Format
file. (One column is the RowType, the rest is the data to be parsed)
- create an index on RowType
- call 11 different SELECT INTO statements based on RowType

2,000,000 rows loaded in 1.5 minutes.

Thanks a lot, BCP works very well.|||(andrewbb@.gmail.com) writes:
> Thanks for the help. I found the fastest way to do it in a single SP:
> - BULK INSERT all data into a 2 column staging table using a BCP Format
> file. (One column is the RowType, the rest is the data to be parsed)
> - create an index on RowType
> - call 11 different SELECT INTO statements based on RowType
> 2,000,000 rows loaded in 1.5 minutes.
> Thanks a lot, BCP works very well.

Hmmm! It's always great to hear when things work out well!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Insertion with single quotes problem

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it is okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

|||

douglas.reilly:

The best bet is to use parameters. Building the SQL String as you are can cause all sorts of problems.

Absent that, you need to "Escape" the apostrophe. SO, do the following...

string testme = "we don''t have anything";

Note there are 2 apostrophes in a row...

Hi Douglas,

The problem is that end-users write those statements and hit insert. And they don't type two apostrophes. I can't "escape" it. Thanks.

blumonde

|||

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

|||

douglas.reilly:

You then have two choices.

1.Use parameters.

2. Search the string for ' and replace it with '' (two apostrophes). Of course users are not going to use two apostrophes.

I will try using parameter first. Thanks.

blumonde

|||

Parameters did it for me. Thanks.

blumonde

inserting\update line numbering

I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.|||If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.

This is a daily build operation

Using Identity though creates the numbering for all records in the table (sees it as one order (500 records, records numbered from 1 to 500?)

I am looking at the line numbering to reset back to 1 everytime there is a change in the order number field (inv_ref field)

this can't be done through identity?|||My guess is, that it would be possible using an identity column, but I wouldn't go for that if it needs to be reset on a daily basis.

It's probably me, but I'm still not quite clear on what you want, on the other hand, maybe I do but miss the point as to why you need a linenumber associated with the table contents.

One of these might work for you though:
- create a view that has a computed column (if the linenumber can be determined on other information from the table);
- create a trigger that does an update (guess this can be quite a burden);
- create an sp; do an update based on identity from a temp-table.|||line number id forms part of the primary key make up.

I have Invoice number, sales order number, and line id

I can't include product id instead of line id as in an invoice there may be a reference to the same product id i.e. at line 1 and 10.

I guess a messy way of going about it is to just use identity and leave the count go on the entire table just to satisfy the primary key requirements.

Inserting/Updating and locking

We are inserting and updating large amounts of rows and find that users are
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
>> We are inserting and updating large amounts of rows and find that
>> users are complaining that their SELECT queries on the same tables
>> are blocking (not finishing) until our INSERT or UPDATE finishes.
>> Is there any way to tell an INSERT or UPDATE to not lock the whole
>> table so that SELECT's can still be run on the table?
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com

Inserting/Updating and locking

We are inserting and updating large amounts of rows and find that users are
complaining that their SELECT queries on the same tables are blocking (not
finishing) until our INSERT or UPDATE finishes.
Is there any way to tell an INSERT or UPDATE to not lock the whole table so
that SELECT's can still be run on the table?
Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
That's probably not the problem. While it's likey that SQL Server is
escalating row locks to page locks and even possibly a table lock if
enough rows are affected by the insert/update, while that transaction is
running, there are exclusive locks on those rows/pages/table.
When a page has an exclusive lock, no other readers or writers can touch
the page. They are blocked until the update/insert transaction
completes. That is, unless they use the read uncommited or NOLOCK table
hint on the tables in the Select statements. But since you are updating
information, is it ok for your users to read dirty data? I don't know.
That's up to you to determine. Dirty data arises when data is
updated/inserted in a transaction and another user reads the data using
read uncommitted isolation level. if the update transaction then rolls
back the changes the user that selected the data is staring at data that
doesn't exist any longer in the database.
The other option is to keep the insert/update transactions as short as
possible. Use batches if you need to to. That will keep the outstanding
locks to a minimim.
SQL Server 2005 offers a method for readers to see the original data
even if it's being updated by another user, but this option will likley
introduce overhead in the database because the data is temporarily
written to tempdb so it's available for other users to read. And it's a
database-wide setting.
For SQL 2000, it's either dirty reads or blocking. But short
transactions mitigate most of these problems.
David Gugick
Imceda Software
www.imceda.com
|||Mike W wrote:
> We are inserting and updating large amounts of rows and find that
> users are complaining that their SELECT queries on the same tables
> are blocking (not finishing) until our INSERT or UPDATE finishes.
> Is there any way to tell an INSERT or UPDATE to not lock the whole
> table so that SELECT's can still be run on the table?
Another thing to consider is where the newly inserted rows are going.
For example, if you have a clustered index on an IDENTITY column, then
inserting new rows will have less of an effect on existing data because
the new most of the rows are inserted on new pages. Updated rows will
still cause problems.
What is your clustered index on? What data are you inserting? Can you
insert and update in different transactions? Can you also update and
insert in small amounts, say 1,000 rows at a time, rather than all at
once?
David Gugick
Imceda Software
www.imceda.com
|||Thanks for your responses. I believe the table that is being updated does
have a Clustered index on the Primary key field, but it's not an Identity
field.
The data we are inserting is customer lead information.
I will ask about the last 2 questions since it is not me that is doing the
updates.
Thanks again.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:On30MJv4EHA.1596@.tk2msftngp13.phx.gbl...
> Mike W wrote:
> Another thing to consider is where the newly inserted rows are going. For
> example, if you have a clustered index on an IDENTITY column, then
> inserting new rows will have less of an effect on existing data because
> the new most of the rows are inserted on new pages. Updated rows will
> still cause problems.
> What is your clustered index on? What data are you inserting? Can you
> insert and update in different transactions? Can you also update and
> insert in small amounts, say 1,000 rows at a time, rather than all at
> once?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Mike W wrote:
> Thanks for your responses. I believe the table that is being updated
> does have a Clustered index on the Primary key field, but it's not an
> Identity field.
> The data we are inserting is customer lead information.
> I will ask about the last 2 questions since it is not me that is
> doing the updates.
> Thanks again.
>
If the clustered index is on something other than a date or an identity,
you have a few potential issues:
1-The clustered index is probably causing page splits as new rows are
inserted. This is a very expensive operation because it requires a page
is split, a new one created, and rows moved around. While this operation
is going on, both pages are locked by SQL Server, adding to the locking
overhead of this operation.
2- The clustered index is likely requiring the disk heads move all
around the physical disk to locate the page to update. There is nothing
slower than random disk access for a database, further slowing down the
operation.
You can mitigate some of the problems here (not the physical disk heads
moving around) by leaving space in your clustered index using a fill
factor. However, this requires you rebuild the clustered index as needed
to maintain the free space before a bulk update of data. Using a fill
factor will leave a percentage of space available in each row and help
prevent page splitting. However, it will make the table a percentage
larger, but this may not be a problem if the free space is going to be
filled with new data anyway.
And the rebuilding of the clustered index will likely affect concurrency
for that table while the rebuild occurs.
If your clustered index is, in fact, on a column or set of columns that
are causing these problem, you may need to consider changing the index
to something that keeps all new data at the end of the table. The other
option is to temporarily remove the clustered index during the load and
rebuild when complete, but this will cause an automatic rebuild of all
non-clustered indexes as well and may be more expensive than what you
want.
A clustered index on an identity can prevent a host of problems and
speed the load process significantly in this case. It's worth a test to
see if it helps the load and if it affects other queries run on that
table.
David Gugick
Imceda Software
www.imceda.com

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind Regards
For double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting Zero instead of Null

Hi Guys,

I have the following SQL Code:

SELECT TOP (100) PERCENT

CAST(Item AS nvarchar(32)) AS Item,

CAST(Customer AS nvarchar(12)) AS Customer,

CAST(Warehouse AS nvarchar(12)) AS Warehouse,

MAX(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END) AS INV1,

MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END) AS INV2,
MAX(CASE WHEN InvMonth = 'INV3' THEN CAST(Qty AS numeric(8, 0)) END) AS INV3,

MAX(CASE WHEN InvMonth = 'INV4' THEN CAST(Qty AS numeric(8,0)) END) AS INV4,

MAX(CASE WHEN InvMonth = 'INV5' THEN CAST(Qty AS numeric(8, 0)) END) AS INV5,
MAX(CASE WHEN InvMonth = 'INV6' THEN CAST(Qty AS numeric(8, 0)) END) AS INV6

FROM MVXReport.DMSExportStage1
GROUP BY CAST(Item AS nvarchar(32)), CAST(Customer AS nvarchar(12)), CAST(Warehouse AS nvarchar(12))
ORDER BY Item, Customer

And i am getting the following Results:

T100 APGL 10 1 6 2 1 3 3
T100 AUTOONE 10 NULL NULL NULL NULL NULL NULL
T100 CBCNSW 10 NULL NULL NULL NULL NULL NULL
T100 CBCQLD 10 NULL NULL NULL NULL 2 3
T100 CBCSA 10 NULL NULL NULL NULL NULL NULL
T100 CBCVIC 10 NULL NULL NULL NULL NULL NULL

I would like to know how to insert a 0 (zero) when the qty is null.. I have tried :

MAX(CASE WHEN InvMonth = 'INV1' THEN (CASE WHEN QTY IS NULL THEN (CAST(0 AS numeric(8, 0))) ELSE CAST(Qty AS numeric(8, 0)) END) END) AS INV1,

But it didnt seem to work.. If someone could point me in the right direction that would be wonderful..

thanks

Scotty

You could use COALESCE function for this task. Try change

CASE WHEN QTY IS NULL THEN (CAST(0 AS numeric(8, 0))) ELSE CAST(Qty AS numeric(8, 0)) END

to COALESCE(QTY,0)

|||

Try something like this:

isnull( Qty, 0 )

in each location where you have just Qty.

|||

Thanks Guys,

I have tried both of your solutions, but i am still getting null values. I can see why, as in the file that i am building these columns from, not every customer/Item every month has a record.. Hence why it is bringing back NULL..

Any other ideas...

thnkas

scotty

|||try this one instead

SELECT TOP (100) PERCENT
CAST(Item AS nvarchar(32)) AS Item,
CAST(Customer AS nvarchar(12)) AS Customer,
CAST(Warehouse AS nvarchar(12)) AS Warehouse,
COALESCE(MAX(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV1,
COALESCE(MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV2,
COALESCE(MAX(CASE WHEN InvMonth = 'INV3' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV3,
COALESCE(MAX(CASE WHEN InvMonth = 'INV4' THEN CAST(Qty AS numeric(8,0)) END),0) AS INV4,
COALESCE(MAX(CASE WHEN InvMonth = 'INV5' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV5,
COALESCE(MAX(CASE WHEN InvMonth = 'INV6' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV6
FROM MVXReport.DMSExportStage1
GROUP BY
CAST(Item AS nvarchar(32)),
CAST(Customer AS nvarchar(12)),
CAST(Warehouse AS nvarchar(12))
ORDER BY
Item
, Customer|||

use

MAX(Isnull(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END,0)) AS INV1

--or

Isnull(MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV2

Inserting XML with SSIS - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

You might want to post your question at SQL Server Integration Services forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1.

Inserting XML values

" Insert Into...Value" seems to be the command for inserting new data into an XML Doc. I have also seen people using "Insert...Value" without the "Into" keyword. Is there a difference between "Insert" and "Insert Into"?

There is no difference. 'INTO' is optional keyword for INSERT ... VALUES() statement. It's not only for insert xml value, but for all other sql types.|||thank you :-)

Inserting XMl string into table.

Hi,

I want the value of a field as an XML string.

Ex: <student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>

Please let me know whether the normal insert command can be used to insert this XML string

I have done with normal insert Command like

Insert INTO listtable list_id, listDetails VALUES 1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>'

Here, the XML string is inserted into 2nd column.

Is this the way to do?

Because, when i read the string back, i am getting the characters &lt; etc. in place of "<", ">" etc.

How the XML string column is selected back to get the correct XML string without junk characters?

Please help me out.

I am not sure what goes wrong but you have not shown how you read out the data exactly.

The following is a sample that creates the table and inserts your sample data and then reads it out with a simple SELECT FROM, that works fine for me with SQL Server 2005 Management Studio Express, the angle brackets <> are certainly not escaped:

Code Snippet

CREATE TABLE #listtable (

list_id int,

listDetails xml

);

GO

INSERT INTO #listtable (list_id, listDetails)

VALUES(1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>');

SELECT list_id, listDetails FROM #listtable;

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
058c0bfd7de&DisplayLang=en" target="_blank">http://www.microsoft.com/downloads/...&DisplayLang=en
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||the problem with inserting that data into the query, is there is more
than 8000 characters in the entire thing, it would take ages to go and
select 8000 at a time. I need something like navicat that works a little
more solid. navicat seems like it is skipping fields... its all a big
pain in my XXX. hah.
if anyone can help. please do..
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
http://www.microsoft.com/downloads/details.aspx?FamilyID=ca1cc72b-6390-4260-b208-2058c0bfd7de&DisplayLang=en
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.
The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
http://www.microsoft.com/downloads/d...DisplayLang=en
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>
|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>
|||the problem with inserting that data into the query, is there is more
than 8000 characters in the entire thing, it would take ages to go and
select 8000 at a time. I need something like navicat that works a little
more solid. navicat seems like it is skipping fields... its all a big
pain in my XXX. hah.
if anyone can help. please do..
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

inserting xml data

Hi,
Is there any way to insert the output of xml_auto into a table

for eg:

select * from categories for xml auto

i need the output of the abouve query to be inserted into another table
the destination table has one column,thomson (saintthomson@.yahoo.com) writes:
> Is there any way to insert the output of xml_auto into a table
> for eg:
> select * from categories for xml auto
> i need the output of the abouve query to be inserted into another table
> the destination table has one column,

I think the only way you can do this in SQL 2000 is to use OPENQUERY:

INSERT tbl (col)
SELECT * FROM OPENQUERY (LOOPBACK,
'SELECT * FROM categories FROM XML AUTO')

Here LOOPBACK is a linked server back to your own, and here is a real
funny thing: you must set it up to use MSDASQL, that is the OLE DB over
ODBC provider! If you use SQLOLEDB which is the recommended provider,
you will get binary data back.

But when I did a quick test, the result was not entirely acceptable, since
the XML string was split up over six rows.

In the next version of SQL Server, SQL 2005, currency in beta, there
are significant enhancements in XML support, including a specific xml
datatype, and you can do this easily.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||xmlbulkload no good?

Inserting without using INSERT INTO

Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKHi
if your concern is to hide sql then alternative is to use StoredProcedure
with encryption.
Regards
R.D
"DNKMCA" wrote:

> Hi,
> How do i insert a value into varbinary column using ASP
> Here i dont want to use "INSERT INTO"
> Tx.
> DNK
>
>

Inserting without using INSERT INTO

Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKSorry, "INSERT INTO" is the answer if you want to insert. Maybe you
could you explain why you don't want to if that's not the answer you
are looking for.
Note however that you insert into *tables* not a column. Perhaps you
just mean you want to update a column in which case use UPDATE.
David Portas
SQL Server MVP
--|||I want to know is there a way i can put a value into VARBINARY Column thru
ASP Code.
-DNK
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126860791.506472.201910@.f14g2000cwb.googlegroups.com...
> Sorry, "INSERT INTO" is the answer if you want to insert. Maybe you
> could you explain why you don't want to if that's not the answer you
> are looking for.
> Note however that you insert into *tables* not a column. Perhaps you
> just mean you want to update a column in which case use UPDATE.
> --
> David Portas
> SQL Server MVP
> --
>|||All updates should normally be done through stored procedures so the
answer is to call a proc using the ADO command object. The proc can
then perform the INSERT.
David Portas
SQL Server MVP
--|||DNKMCA (dnk@.msn.com) writes:
> I want to know is there a way i can put a value into VARBINARY Column thru
> ASP Code.
> -DNK
If you want a sample about ASP, you may want to ask in an ASP forum.
From the SQL side of things, varbinary is not that much different than
any other data value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. Mark
This is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>
|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!
|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. MarkThis is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>