The only SQLCLR feature I haven't found a good use for so far is the SQLCLR trigger. Would anyone care if those were removed?

What I would like to see...

But the idea is to insert...

USE tempdb;

DECLARE @data

TABLE (

input_month DATETIME NOT NULL,

price ...

You are welcome. I particularly like the MERGE syntax - and it produces an arguably neater plan, avoiding a table spool. Do let us know how you get...

Isn't the academic year just YEAR(DATEADD(MONTH, -7, {date_time}))?

USE tempdb;

CREATE TABLE #Data

(

date_time DATETIME ...

In any case, here are a couple of alternatives:

-- New Transactions

CREATE TABLE #Transactions

(

TranIdBIGINT PRIMARY KEY,

TranDateDATETIME,

TranCodeVARCHAR(4),

AmountMONEY

);

-- Existing Transactions

CREATE TABLE #VendorExport

...

I assume:

INSERT INTO @VendorExport

SELECT * FROM @Transactions

WHERE NOT EXISTS(SELECT TranId FROM @Transactions)

should say:

INSERT INTO @VendorExport

SELECT * FROM @Transactions T

WHERE NOT EXISTS(SELECT * FROM @VendorExport VE WHERE VE.TranId = T.TranId)

...?

IGNORE_DUP_KEY seems like a magic bullet doesn't it? Be aware though that nothing is for free. When this option is enabled on an index, SQL Server has to...

Awful. Just awful.

asashank82 (2/1/2010)

---

actually i am passing this as a parameter from ASP.net application as a string and then in the Stored Procedure, i want to split the string and return a...

Flo's second option (don't insert duplicates) is normally by far the best option of those presented so far.

The best way to achieve that depends on factors which you have not...

Also consider not storing such strings in the database in the first place. If the original source of this data is a file, you can use any of the...

Lynn,

Yes. DATETIME2 does not support the add operator:

[font="Courier New"]Msg 8117, Level 16, State 1, Line xx

Operand data type datetime2 is invalid for add operator.[/font]

Adding dates directly has always seemed...

Oh right - well thanks for that.

Hey Flo,

Good to see you around on here again.

Excellent point about the limitation of INSERT...EXEC - the inability to nest calls is the main reason I try to avoid this...