Database

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 

Vipul Mehta wrote:

Is there ant in built query where in I can get all the field names from all the tables of my DB in SQL 2005

insert into BracingKits (PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder, <br />
S3TL29, S3TLVL, S3TLUL, S3TLMD, S3TLHD, S3TLVH, S3TLUH,S3R, S4TL,S4A, [1200TLWD],[1800TLWD], [1800SRWD],<br />
[2400SRWD],[3600SRWD] , [4400SRWD])<br />
SELECT PartID, SectionNumber, TopSread, BottomSpread, BracingBoltDiameter, PickOrder from SSVLSStandardBracingDesign