>>>>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???

Is RowNumber a field in your table? Or you mean the @RowCount variable?

@RowNum = @RowCount (or @RowNum = RowNum -- if RowNum is a field).