Not sure what your Split function does, so here's a self-contained version which brings all the processing in-line

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t;

SELECT '123; 156' AS StaffNo,...

Is this what you're looking for?

Declare @staffno varchar(10)

Set @staffno = '156'

SELECT TableName.*, S.Item

FROM TableName

CROSS APPLY dbo.split(staffno, ';') AS S

WHERE S.Item = @staffno

You could use OPENROWSET BULK to achieve this

IF (SELECT CHARINDEX('Error', BulkColumn) FROM OPENROWSET (BULK 'C:\YourFile.txt', SINGLE_CLOB) AS Z) > 0

SELECT 'ERROR IN FILE'

Try this

WITH cteDealer (Dealer_locationid, dealerid, locationid) AS

(

SELECT 1001, 101, 501 UNION ALL

SELECT 1002, 101, 502 UNION ALL

SELECT 1003, 101,...

nagarajan.santhan (9/7/2010)

---

Hi

I have a text file having new records with random columns...

say example...

1,LASTNAME,FIRSTNAME,AGE,SEX,DOB

2,LASTNAME,FIRSTNAME

3,LASTNAME,FIRSTNAME,AGE,SEX

4,LASTNAME,FIRSTNAME,AGE,SEX,DOB

5,LASTNAME,FIRSTNAME,AGE

6,LASTNAME

When using BULK INSERT command it says "Bulk insert data conversion error (type mismatch) for row 2,...

emily-1119612 (9/7/2010)

---

I am not very familiar with OPENROWSET BULK.

I also should have mentioned that not every record would have embedded quotes. The following would be more representative.

"A","Is this a ""valid""...

vinod.km (9/6/2010)

---

Hi All,

I have an existing table, which has thousands of records.There is no identity column in this table & I can not modify the existing table structure also. The...

steve-893342 (9/4/2010)

---

SELECT *

FROM...

Gordon Barclay (9/5/2010)

---

It would seem that joining the date and time is the only way;

SELECT

CONVERT(char(10), YearChar, 103) + ' '+ CONVERT(Char(8), tbl_DateTime.Time,13),

Cast(tbl_DateTime.Time as char(8))

FROM

tbl_DateTime

WHERE

...

sku370870 (9/5/2010)

---

Thanks for your replies.

If the Inserted and Deleted tables always exist for the duration of a trigger ... if you write a trigger for INSERT, UPDATE and DELETE -...

Is this what you're looking for?

SELECT *

FROM tbl_DateTime

WHERE CONVERT(DATETIME, YearChar, 103) + Time BETWEEN '20100823 22:00:00' AND '20100824 06:00:00'

Precisely, and moreover the way the trigger is written for a DELETE, when the Audit table is populated, the fields ProductID, BatchID, Note, LocationID will always end up as NULL...

sku370870 (9/4/2010)

---

Oops! I've been doing Maths since I was 5 and it never occurred to me it meant 'join on the ProductIDs minus 1'.

But, the code is in a...

Another approach is to create a function in situ (here called Z) to split the column in to its component parts and then CROSS APPLY against this function which is...

ackreul (9/3/2010)

---

Again, the column where the data is held has filestream enabled, so the actual data will not be stored in the database, storing only the paths is not an...