SQL Import Question

I'm in the process of writing SQL to move our existing Access database [Upworks] to a new SQL database for ConnectWise.

Due to the structure of the tables not being in standard row/column format, I'm having to make passes at importing the data into the structure it needs to be in- but there are some discrepancies in the values that I would like to be able to adjust on the fly.  For example:

/* Declare vNumber as the operator for the per row run */
/* Draw this value from whatever we determine to be the primary key of the corresponding Upworks Column */
DECLARE @vNumber INT
SET @vNumber = (SELECT MIN([ContactID]) FROM [Upworks].[dbo].[Contacts])

/* Find the Maximum value */
DECLARE @MaxNumber INT
SET @MaxNumber = (SELECT MAX([ContactID]) FROM [Upworks].[dbo].[Contacts])

WHILE
@vNumber <= @MaxNumber
BEGIN
	INSERT INTO Company_User_Defined_Field_Value (Company_User_Defined_Field_Value_RecID, Company_RecID, User_Defined_Field_RecID, User_Defined_Field_Value, Last_Update_UTC, Updated_By)
	VALUES (
		(SELECT ISNULL(MAX(Company_User_Defined_Field_Value_RecID)+1,1) FROM Company_User_Defined_Field_Value),
		--Company_RecID,
		(SELECT Company_RecID
		FROM [cwwebapp_uptimelegal_new].[dbo].[Company] 
		WHERE Upworks_ContactID = @vNumber
			),
		2,
		(SELECT Source
		FROM [Upworks].[dbo].[Company] 
		WHERE ContactID = @vNumber
			),
		GETDATE(),
		'Upworks'		
		
		)

	SET @vNumber = @vNumber + 1
END

Open in new window


This part:

(SELECT Source
		FROM [Upworks].[dbo].[Company] 
		WHERE ContactID = @vNumber
			)

Open in new window


Pulls the source (Google, Facebook, etc.) into the new table, but the source value is actually a number (1 - 9) in the old table.  What I would like to be able to do is alter this so I can define a value based on whether it is 1-9 in the old table as it gets imported to the new one.

Any help is appreciated.
LVL 6
Uptime Legal SystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Do you have a table for the source values? If yes then join it with the table, like this:
(SELECT st.SourceValue
FROM [Upworks].[dbo].[Company] c INNER JOIN SourceValues sv ON c.Source = sv.Source
WHERE c.ContactID = @vNumber)

Open in new window

If you do not have a table with the values then you need to use a CASE statement:
(SELECT CASE Source 
   WHEN 1 THEN 'Google'
   WHEN 2 THEN 'Facebook'
   WHEN 3 THEN 'Twitter'
   ELSE 'Unknown' END
FROM [Upworks].[dbo].[Company] 
WHERE ContactID = @vNumber)

Open in new window

However, if you want that your new database follow the principle of Normalisation you would keep the Source as an index, like in the old database

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Uptime Legal SystemsAuthor Commented:
That works, appreciate the inner join method as well-- some of the instances are simply changing 1's and 0's to trues and false's but some of them are like the example above and have multiple options.

Of course, I would love to keep it the same way it is (the source has tables) but the ConnectWise (application) database is already structured in a specific way and I have to accommodate it (frustrating).

(the tables are laid out like this)
http://filedb.experts-exchange.com/incoming/2015/10_w44/979401/sqltables.PNG

Thanks for the response!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.