Database

Glen Harvy wrote:

my assumption that once a dataset is created and tables loaded I assume another datatable referencing tables within the dataset is only going to increase resource usage minimally

ayyp wrote:

is it possible

CREATE TRIGGER CheckIDCol ON dbo.MyTestTable 
FOR INSERT
AS

declare @IDVal as int
declare @nCount as int

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

select @IDVal =  max(idcolumn) from MyTestTable (TABLOCKX) --<code>bad idea</code>

set @nCount = 1

while (@IDVal >= @nCount)
begin
	if not exists(Select IDColumn from MyTestTable where IDColumn = @nCount)
	begin
		break 
	end
	set @nCount = @nCount + 1
end

update MyTestTable set idcolumn = @nCount where idcolumn = 0

COMMIT TRANSACTION

SELECT 
    [DATE] as tblS_Date, 
    MID([2ND REFERENCE_],1,9) as tblS_CellNr, 
    MID([Cell Number],2,9) as tblB_CellNr, 
    [Total___] as tblB_Total 
FROM 
    Statement as tblS 
INNER JOIN 
    Billing as tblB 
    ON MID(tblS.[2ND REFERENCE_],1,9) = MID(tblB.[Cell Number],2,9)

Michael Potter wrote:

You should be able to put your MID functions in the JOIN clause:

SELECT
[DATE] as tblS_Date,
MID([2ND REFERENCE_],1,9) as tblS_CellNr,
MID([Cell Number],2,9) as tblB_CellNr,
[Total___] as tblB_Total
FROM
Statement as tblS
INNER JOIN
Billing as tblB
ON MID(tblS.[2ND REFERENCE_],1,9) = MID(tblB.[Cell Number],2,9)

playout wrote:

How would the select/join statement look like

SELECT tblEmployees.*, tblLoanAmount.*
FROM [C:\costToCompany.mdb].tblEmployees 
LEFT JOIN [C:\Accounts.mdb].tblLoanAmount ON tblEmployees.EmployeeID = tblLoanAmount.EmployeeID