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