Inserting data from varchar column to Int column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting data from varchar column to Int column

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2008-12-05 : 03:41:46

Hi,
I have 2 tables as follow

create table tb1
([SSNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

create table tb2
([BSSNo] [int] NULL)

and tb1 have following data
3
2
6
11
9
8

I am inserting this data into tb2.BSSNo which have int datatype.
and it is inserting sucessfully.

insert into tb2
(BSSNo)
select
isnull(t1.SSNo,'UNKWN')
from tb1 t1

Plz tell why it is happened as i am inserting the from varchar column to int column ?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-05 : 04:29:01

The varchar column can contain strings and integers etc but the Int column can only take integers. An insert will work as long as the table being inserted accepts the data (i.e. it meets or can be easily converted to meet its datatype).
If you put characters eg 'x','y','z' into the varchar column the same insert you ran will fail. The datatype of the column doesn't mark the data as a particular type it just limits what can be inserted.
This would also be the case e.g. if you had '1 Jan 2008' in a varchar column - this could be inserted into a datetime column in the same way.

abc123
Starting Member

47 Posts

Posted - 2008-12-05 : 04:33:19

(i.e. it meets or can be easily converted to meet its datatype). -- this convert will perform at server level or not
also is it do to some server checks?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 05:00:47

It's an implicit conversion. SQL server will know to implicitly (you don't have to tell it to) convert between certain datatyps.

In this case it is implicitly casting the VARCHAR value to INT's. That's fine if you only have values that *will* cast successfully to an INT. Some datatype's cannot be implicitly cast to others and you would have to state

CAST(x AS y)

CONVERT(y, x)

Check out:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

for a more detailed explanation.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 05:14:29

Sorry -- the link should probably be:

http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

instead.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources