I have a temp table that I imported using SSIS. I was having some trouble importing the data as anything other than varchar. The end result is to take the data in this table and copy it to 3 other tables but I need to copy some of the varchar columns as numeric and some as datetime.
I also have 4 columns called century, year, month and day that need to be concatenated and copied to another table as a datetime.
Thanks in advance.
Scott
Not enough information. What trouble are you having?
Also, please post:
In general, you would use CAST or CONVERT to convert the data from VARCHAR to numeric. Post some examples of your input data and the output you need to get more details.
The varchar that needs to be converted to string are numbers ranging anywhere from 1-12 digits. Here is a sampling of a few of the columns
183 67 Delete 5912 Drug Stores (Pharmacy) 100146458 2 5/7/1982 0:00
191 67 Delete 5993 Cigar, Cigarette, Tobacco Stor 0 1 11/17/1981 0:00
205 67 Deleted 5814 Fast food 0 1 11/10/1996 0:00
213 52 Term Outstanding Bal 5921 Liquor Store 0 0 1/1/1984 0:00
221 52 Term Outstanding Bal 0 Unknown or blank code 0 0 4/26/1982 0:00
230 57 C/O Comp Agent Dele 5814 Fast food 0 0 5/7/1982 0:00
The tables I am copying into are already created.
Did you mean converted to NUMERIC? If you really meant "converted to string", there is nothing further you need to do. VARCHAR is already string.
If you do want to convert 12 digits to NUMERIC, what is the data type of the destination column(s), specifically those that can be 10,11 and 12 digits long? The maximum an int can store is some 10-digit number that starts with a 2.
One thing that possibly might work is to simply select from your staging table and insert into your destination table(s) without you doing any conversions. The varchar columns should implicitly be converted to numeric. If that does not work - i.e., if you are getting some errors, that means there are some strings (varchar values) that cannot be converted to the numeric data appropriate for that column. There are some limitations to implicit conversions - the table on this page shows what can be converted to what.
We add two columns to that type of Staging Table:
ErrorNo
ErrorMsg
and we run some SQL on the Staging Table to check if the data in conforms, such as "Should be a valid date" or "Should be numeric". Any rows that don't pass the test get an error number (first error found only) and a concatenated list of error messages. We then don't import any rows where ErrorNo <> 0 and we have a report that the user can run to see the ErrorMsg (and data) on rows that have an error - so they can fix up the data in the source system before the next import.
Yes - I meant Numeric.
The columns in the table I need to copy to are also numeric.
I will try copying anyway and see what happens.
I may not get to this before I leave today and then have a long weekend so I will update you next week.
Thanks for the suggestion.
Don't rely on IsNumeric() to test a string as being a valid numeric value. There are invalid strings that it gives a false-positive result for 
A reg-ex expressions is usually good enough e.g.
SELECT CASE WHEN TestValue LIKE '%[^-0-9.]%' THEN 1 -- Non numeric character
WHEN TestValue LIKE '_%-%' THEN 2 -- minus sign not at the front
WHEN TestValue LIKE '%.%.%' THEN 3 -- two or more decimal places
ELSE 0
END AS [Test],
IsNumeric(TestValue) AS [IsNumeric],
'"' + TestValue + '"'
FROM
(
SELECT '12345' AS TestValue
UNION ALL SELECT 'A12345'
UNION ALL SELECT 'A2345A'
UNION ALL SELECT '123A45'
UNION ALL SELECT '-12345'
UNION ALL SELECT '12345-'
UNION ALL SELECT '123-45'
UNION ALL SELECT '.12345'
UNION ALL SELECT '12345.'
UNION ALL SELECT '123.45'
UNION ALL SELECT '.123.45'
UNION ALL SELECT '123.45.'
UNION ALL SELECT '1E1' -- Scientific format, very unlikely that would be valid in your data!
UNION ALL SELECT '1.1'
UNION ALL SELECT '1,1'
UNION ALL SELECT '1,000'
UNION ALL SELECT ' 1' -- This might reasonably be included as "allowed"
UNION ALL SELECT '$1' -- might reasonable be included, but won't convert to NUMERIC
) AS T
--
ORDER BY [Test], [IsNumeric], TestValue