Hi

Here the script I just tested:

IF (OBJECT_ID('tempdb..#tmpPOH') IS NOT NULL)

DROP TABLE #tmpPOH

declare @cXML xml

select @cXML = (select * from AdventureWorks.Purchasing.PurchaseOrderHeader

for xml raw('item'), type, elements, root('root'))

DECLARE @now DATETIME

SET...

Hi Bob

I think in this case a usual inline function may be one of the fastest solutions.

Here my function

USE tempdb

IF (OBJECT_ID('dbo.ufn_CompareBH') IS NOT NULL)

DROP FUNCTION dbo.ufn_CompareBH

GO

CREATE FUNCTION...

Are you able to compare on a table or do you have to match each single text?

How many data will come within one import action?

Greets

Flo

Hi Bob!

Before diving into a SQL parser... What about a CLR function? Would this be a possibility?

Greets

Flo

Hi

I don't know any way to include code blocks. Maybe think about moving this code into a UDF or helper procedure.

Greets

Flo

Hi

Here a little example:

USE tempdb

IF (OBJECT_ID('dbo.ufn_test1') IS NOT NULL)

DROP FUNCTION dbo.ufn_test1

GO

CREATE FUNCTION dbo.ufn_test1 (@id INT)

RETURNS TABLE

AS

RETURN

(

...

Hi

Try the DATALENGTH function. This should also work with TEXT data.

DECLARE @T TABLE (txt TEXT)

INSERT INTO @T VALUES ('hello world')

SELECT DATALENGTH(txt) FROM @t

Hint 1

If you need to use NTEXT you...

Hi Gail

There are two reasons for this ugly approach:

First the database design

The database is not my design it came by my previous chief developer. I would call it not even...

Hi

The problem is that the temp table already exists at second execution and the engine and now it first determines if the INSERT fits to the available columns.

There are two...

If your statements don't too much changes you can work with transaction rollbacks:

IF (@@TRANCOUNT != 0)

ROLLBACK TRANSACTION

BEGIN TRANSACTION

INSERT INTO anywhere VALUES (1, 'Hello World')

ROLLBACK TRANSACTION

So everything becomes...

GilaMonster (3/18/2009)

For what reason? Does the person who asked know about the potential risks with the READ UNCOMMITTED isolation level?

To SQL, READ UNCOMMITTED doesn't mean 'get my data quickly'...

Hi

Could you explain "just died"?

I tried your example and id took 810 milliseconds on my workstation for 4012 rows. It's a quiet good pc but it's no server.

Greets

Flo

Hi

The shown "text" is only a invalid value. Try this extension of my previous example to see the written bytes:

DECLARE @t TABLE (txt VARCHAR(12), ntxt NVARCHAR(12))

INSERT INTO @t VALUES...

The question is absolutely not ridiculous 🙂

Sorry I didn't read correct and notice that you want to use this on a whole table. In this case I only know a...

Hello Venki

Seems that the trigger tries to write incorrect data into table "Dept.dbo.department". Can you post the trigger source?

Greets

Flo