>>>Ok, Not sure what you mean. Can you elaborate a bit?
>>>
>>>It would probably be easier if you posted a full example of one of your loops and what you want to do with the variables, and maybe we can go from there.
>>
>>Ok, it's a bit long...
>>
>>The data is coming from an Access contact mgmt system a client of mine wrote. The data's not at all normalized. There are multiple
>>columns for phone numbers. Columns like 'Played', 'Venue', and 'Surfer' are going to end up in a table called 'Filters'. I will need to split CityState into 2 columns. The variables below describe the data. There are 4 files, and I was going to do this once for each. This is soley to get the single table into the new tables.
>>
>>

>>DECLARE @NumberRecords	int
>>DECLARE @RowCount int
>>DECLARE @Name nvarchar(50)
>>DECLARE @Company int
>>DECLARE @Category nvarchar(50)
>>DECLARE @Subcategory nvarchar(50)
>>DECLARE @HomePhone int
>>DECLARE @WorkPhone nvarchar(56)
>>DECLARE @Extension nvarchar(50)
>>DECLARE @FaxNumber nvarchar(50)
>>DECLARE @CellPhone nvarchar(50)
>>DECLARE @OtherPhone1 nvarchar(50)
>>DECLARE @OtherPhone2 int
>>DECLARE @OtherPhone3 int
>>DECLARE @Street1 nvarchar(50)
>>DECLARE @CityState nvarchar(50)
>>DECLARE @ZipCode float
>>DECLARE @XmasList bit
>>DECLARE @Srf int
>>DECLARE @Surfer int
>>DECLARE @Client int
>>DECLARE @Directions varchar(250)
>>DECLARE @Email nvarchar(50)
>>DECLARE @Email2 nvarchar(50)
>>DECLARE @WebSite nvarchar(50)
>>DECLARE @Music int
>>DECLARE @Played int
>>DECLARE @Venue int
>>DECLARE @Notes varchar(250)
>>DECLARE @Children int
>>DECLARE @LiquidBlue int
>>DECLARE @FromTable varchar(8)
>>
>>
>>-- Pull the records into a temp table
>>SELECT * 
>>	INTO #TmpContactInfo1
>>	FROM ContactInfo1
>>
>>-- Get the number of records in the temporary table and
>>-- create a counter variable
>>SET @NumberRecords = @@ROWCOUNT
>>SET @RowCount = 1
>>
>>-- Loop through all records in the temporary table using the WHILE loop construct
>>WHILE @RowCount <= @NumberRecords
>>BEGIN
>>
>>	SELECT	Name = @Name,
>>			Company = @Company,
>>			Category = @Category,
>>			Subcategory = @Subcategory,
>>			HomePhone = @HomePhone,
>>			WorkPhone = @WorkPhone,
>>			Extension = @Extension,
>>			FaxNumber = @FaxNumber,
>>			CellPhone = @CellPhone,
>>			OtherPhone1 = @OtherPhone1,
>>			OtherPhone2 = @OtherPhone2,
>>			OtherPhone3 = @OtherPhone3,
>>			Street1 = @Street1,
>>			CityState = @CityState,
>>			ZipCode = @ZipCode,
>>			XmasList = @XmasList,
>>			Srf = @Srf,
>>			Surfer = @Surfer,
>>			Client = @Client,
>>			Directions = @Directions,
>>			Email = @Email,
>>			Email2 = @Email2,
>>			WebSite = @WebSite,
>>			Music = @Music,
>>			Played = @Played,
>>			Venue = @Venue,
>>			Notes = @Notes,
>>			Children = @Children,
>>			LiquidBlue = @LiquidBlue,
>>			FromTable = @FromTable
>>		FROM #TmpContactInfo1
>>		WHERE RowID = @RowCount
>>
>>	SET @RowCount = @RowCount + 1
>>END
>>
>>DROP TABLE #TmpContactInfo1
>>

>
>I don't see anything done here. And also, if you wanted to populate the variables, then the syntax should be reversed, @FromTable = FromTable, etc. (each variable name = Field Name).
>
>And, as Kevin said, in SQL Server you don't want to use RBAR (as Jeff Moden calls it) approach (Row By agonizing row).
>
>You probably need to look into SSIS if you want to migrate your data from Access.

So how do I get the Row number as part of my query???