>>>>>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).


This is it:

SELECT	ROW_NUMBER() OVER(ORDER BY Name) AS RowId, *
	INTO #TmpContactInfo1
	FROM ContactInfo1