Providing your NA_BANKACCTTYPELOOKUP table has 7 columns, all wide enough to accommodate the data, then this should work

BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP

FROM 'C:\TEST\WXBankAcctType.txt'

WITH

(

FIELDTERMINATOR = '|'

)

Thanks for the feedback Mare. I appreciate it:-)

I have managed to come up with an ACCESS variation which is self-contained without the requirement of a physical tally table. I have created a derived tally table...

Actually I wanted to come up with an ACCESS version which uses a virtual tally table so that the whole query was self-contained. However I'm not sure whether...

As an UPDATE

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

CREATE TABLE #t

(Firstname VARCHAR(100),

Lastname VARCHAR(100),

url VARCHAR(200))

INSERT #t (Firstname, Lastname)

SELECT 'David', 'Glance' UNION ALL ...

Does this give you what you're looking for?

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

SELECT 'David' AS Firstname, 'Glance' AS Lastname into #t UNION ALL ...

BTW did you try running the below as a SQL PASS-THROUGH query as I suggested previously? That should work fine too;-)

SELECT ProdColortbl.ProductNbr, ProdColortbl.Date, Z.Color, Z.Units

FROM ProdColortbl

CROSS APPLY

(

SELECT 'Red', Red UNION...

OK so here it is, an ACCESS UNPIVOT query. You will need to create a table called Tally with a single field N, Data Type Number. Simply enter...

Looks to me like you are still missing a quote either side of @rolepwd

You realise that

select @roleval = name, @rolepwd = cast(datalength(name) as varchar(20)) + left(pwd,2) from test

will only be meaningful if you have only one record in test since if you have...

Do you have any other triggers associated with these tables?

If you insert multiple records into TAAdditionalWork then you will get "duplicate" entries based on AuditDate in your Audit table. However I would not expect to see duplicates across the...

Excellent! Looks like you nailed it:cool:

Using OPENROWSET BULK you can specify the columns in any order you like, as if you were selecting from a table eg

SELECT STATE, COUNTRY, LAST_NAME, FIRST_NAME FROM OPENROWSET (BULK...

From what you are telling me, it is clearly absolutely fine to use FIRSTROW = 2. Yes it is crazy fast!:crazy: Format files can be a bit tricky...