datatype Bit and null - SQL Server Forums

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

datatype Bit and null

Author

Topic

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-19 : 12:01:55

Hi,

I have two tables in one table's field I have the datatype bit and null.
In 2nd table's I have the datatype varchar 10.
Now I want to Select the value of 2nd table in my Controle that is Dropdown and insert this value in the 1st table like bit. as this possible.
Thanks for helping.

Navi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-19 : 12:26:55

Not sure what your actual question is but if you want to store a value in a nullable bit column then the only values possible are: <NULL>, 0, and 1. So if those are the only possibilities in your varchar(10) source column then yes, otherwise no

Be One with the Optimizer
TG

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-19 : 15:48:24

Hello,
If the data consists in second tables is like [varchar(10) in the following format (1,2,3......9999999999)], then only u database will alow the insert operation using CAST/CONVERT

I am providing u a sample insert statement....please go through it
INSERT INTO TABLE1(COL1)
SELECT CAST(COL1 AS BIT) FROM TABLE2

If the data consists in second tables is alphabet or any other type then its impossible.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-19 : 17:44:24

quote:
Originally posted by subrata4allfriends

Hello,
If the data consists in second tables is like [varchar(10) in the following format (1,2,3......9999999999)], then only u database will alow the insert operation using CAST/CONVERT

I am providing u a sample insert statement....please go through it
INSERT INTO TABLE1(COL1)
SELECT CAST(COL1 AS BIT) FROM TABLE2

If the data consists in second tables is alphabet or any other type then its impossible.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

Well, you're right that any of the values in you example will convert without a sql error. However the resulting value will always be "1".

Be One with the Optimizer
TG

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-20 : 05:08:47

quote:
Originally posted by TG

quote:
Originally posted by subrata4allfriends

Hello,
If the data consists in second tables is like [varchar(10) in the following format (1,2,3......9999999999)], then only u database will alow the insert operation using CAST/CONVERT

I am providing u a sample insert statement....please go through it
INSERT INTO TABLE1(COL1)
SELECT CAST(COL1 AS BIT) FROM TABLE2

If the data consists in second tables is alphabet or any other type then its impossible.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

Well, you're right that any of the values in you example will convert without a sql error. However the resulting value will always be "1".

Be One with the Optimizer
TG

............Yes, that's right, but if there exist '0' in TABLE2 column then only the resulting value will be '0', else '1'.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-21 : 01:25:00

Sorry I can not understand your meanings,

My question is taht I have 2 tables. In first table the fields datatype is bit and null mode. I want to use null. Therefore I have created a second table, In which I two field(columns) 1st field's name and its dataype as per 1st table. In 2nd field's name was different and its datatype is varchar(10). This table have only three rows that are
1 = True
0 = False
<null> = False
I only want to get values from 2nd table as true, false and null and save in the 1st table as 1 , and <null>.
What are u saying about it.

Navi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-21 : 03:31:16

quote:
Originally posted by feejaz

Sorry I can not understand your meanings,

My question is taht I have 2 tables. In first table the fields datatype is bit and null mode. I want to use null. Therefore I have created a second table, In which I two field(columns) 1st field's name and its dataype as per 1st table. In 2nd field's name was different and its datatype is varchar(10). This table have only three rows that are
1 = True
0 = False
<null> = False
I only want to get values from 2nd table as true, false and null and save in the 1st table as 1 , and <null>.
What are u saying about it.

Navi

Considering you have only a single bit field in table 1, How can you store true,false,... as values inside bit field?

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-21 : 04:03:14

I want to get these values show to user not storing in the single bit field. true,false and null is for showing and 1,0 & <null> for saving in the database. As I said I have two fields in the 2nd table 1st is same like in the first table to compare and 2nd field is true, false and null.

If you are not understanding my question plz. tell me how can I explain you.

Thanks

Navi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-21 : 10:06:03

So you objective is to have a ComboBox that has 2 values: "True" and "False". Based on user selection you want to populate a BIT column, right? (true=1 and false=0). Do you really need the lookup table for just 2 static values?

Be One with the Optimizer
TG

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-22 : 01:50:49

Yes you are right but there is three static values (true=1, false=0, Null= <null>).

Navi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-22 : 08:43:26

ok - (just decide which way you want it

)

quote:
This table have only three rows that are
1 = True
0 = False
<null> = False

quote:
there is three static values (true=1, false=0, Null= <null>).

Then call a stored procedure that applies the user selection to your table. I don't think a lookup table is necessary for your 2 (or 3) possible values.

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-22 : 19:28:49

Are you asking how to do the conversion from VARCAHR(10) to get 1,0 and NULL as datatype BIT? If so try this: CAST(NULLIF(MyColumn, '<null>') AS BIT)

For example:

DECLARE @MyTable TABLE(MyColumn VARCHAR(10))

INSERT @MyTable 
SELECT '<null>'
UNION ALL SELECT '1'
UNION ALL SELECT '0'

SELECT CAST(NULLIF(MyColumn, '<null>') AS BIT)
FROM @MyTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources