July 22, 2013 at 1:40 pm
I have a database that imported some students' info from a text file.
When importing into the raw table, the studentID is set as varchar(9).
Then we select from the raw table into another processing table.
In that table the student id is integer.
Do I need to do explicitly use cast? - that is cast it into int, or I don't need to since studentIDs are all numbers?
It may do implicitly conversion by itself?
what is the better practice
Thanks
July 22, 2013 at 1:47 pm
sqlfriends (7/22/2013)
I have a database that imported some students' info from a text file.When importing into the raw table, the studentID is set as varchar(9).
Then we select from the raw table into another processing table.
In that table the student id is integer.
Do I need to do explicitly use cast? - that is cast it into int, or I don't need to since studentIDs are all numbers?
It may do implicitly conversion by itself?
what is the better practice
Thanks
It would be best practice if you imported into the proper datatype. Since you didn't do that it doesn't really make any difference when you move this into the other table. It will perform an implicit cast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 1:48 pm
You don't need to cast it, an implicit conversion will transform strings into integers if the column only contains digits. If there's a non-integer value in your column, it will fail.
However, IMO a good practice would be to cast it.
July 22, 2013 at 1:50 pm
Thanks, what is IMO
July 22, 2013 at 1:58 pm
sqlfriends (7/22/2013)
Thanks, what is IMO
IMO = In my opinion
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 1:59 pm
Thanks all!
July 22, 2013 at 4:30 pm
You don't need to explicitly cast it in this case, because integer has a higher precedence than varchar, so SQL will implicitly force the varchar to integer.
You should check the column for only digits, though, to insure the value is valid before trying to load the table: as noted, you will get errors if the value can't be translated to an integer.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 22, 2013 at 5:27 pm
Thanks much
July 23, 2013 at 8:02 am
Sean Lange (7/22/2013)
sqlfriends (7/22/2013)
I have a database that imported some students' info from a text file.When importing into the raw table, the studentID is set as varchar(9).
Then we select from the raw table into another processing table.
In that table the student id is integer.
Do I need to do explicitly use cast? - that is cast it into int, or I don't need to since studentIDs are all numbers?
It may do implicitly conversion by itself?
what is the better practice
Thanks
It would be best practice if you imported into the proper datatype. Since you didn't do that it doesn't really make any difference when you move this into the other table. It will perform an implicit cast.
Unless I miss my guess, the OP is importing the flat-file data into a staging table with studentID defined as varchar(9), even though he expects the values to be integers, then moving the data to the actual target table where studentID is defined as int. I find that to be a good practice when importing unvalidated flat-file data - if the transfer from the staging table to the target table fails because of a data type conversion error, it's a LOT easier to find the bad value in a staging table in the DB than it is to dig through even a few thousand rows in a flat file to find the offending value.
Jason Wolfkill
July 23, 2013 at 8:24 am
wolfkillj (7/23/2013)
Sean Lange (7/22/2013)
sqlfriends (7/22/2013)
I have a database that imported some students' info from a text file.When importing into the raw table, the studentID is set as varchar(9).
Then we select from the raw table into another processing table.
In that table the student id is integer.
Do I need to do explicitly use cast? - that is cast it into int, or I don't need to since studentIDs are all numbers?
It may do implicitly conversion by itself?
what is the better practice
Thanks
It would be best practice if you imported into the proper datatype. Since you didn't do that it doesn't really make any difference when you move this into the other table. It will perform an implicit cast.
Unless I miss my guess, the OP is importing the flat-file data into a staging table with studentID defined as varchar(9), even though he expects the values to be integers, then moving the data to the actual target table where studentID is defined as int. I find that to be a good practice when importing unvalidated flat-file data - if the transfer from the staging table to the target table fails because of a data type conversion error, it's a LOT easier to find the bad value in a staging table in the DB than it is to dig through even a few thousand rows in a flat file to find the offending value.
Rereading the OP I think you are right and I agree that it is far easier to find bad values in sql than in a flat file. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 9:27 am
Unless I miss my guess, the OP is importing the flat-file data into a staging table with studentID defined as varchar(9), even though he expects the values to be integers, then moving the data to the actual target table where studentID is defined as int. I find that to be a good practice when importing unvalidated flat-file data - if the transfer from the staging table to the target table fails because of a data type conversion error, it's a LOT easier to find the bad value in a staging table in the DB than it is to dig through even a few thousand rows in a flat file to find the offending value.
Rereading the OP I think you are right and I agree that it is far easier to find bad values in sql than in a flat file. :-)[/quote]
Thanks, yes, you are both right, that's the reason we import to a raw data table first, then use sql to deal with some data type conversion.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply