how to create column with bit data type. set default to "1" in sql server

Posted by Dn2010 under Sql Server on 1/13/2011 | Points: 10 | Views : 50721 | Status : [Member] | Replies : 8
hi friends,

how to create column with bit data type. set default to "1" in sql server.

please give the example.

thanks.




Responses

Posted by: PandianS on: 1/13/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Creating New Table:
CREATE TABLE Tb_Table1

(
ID INT,
BitColumn BIT DEFAULT 1
)
GO

INSERT Tb_Table1(ID) VALUES(10)
GO
INSERT Tb_Table1 VALUES(11,0)
GO

SELECT * FROM Tb_Table1
GO
Adding Column in existing Table:
ALTER TABLE Tb_Table1 ADD BitColumn	BIT DEFAULT 1

GO
Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 1/13/2011 [Member] Starter | Points: 25

Up
0
Down
thanks for your response,

how to bit data type in table in normal sql query not stored procedure.

&

how to insert table in bit datatype.

please give the sample for it.

thanks.



Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 1/13/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

To Insert record:
INSERT Tb_Table1(ID) VALUES(10)

GO
INSERT Tb_Table1 VALUES(11,0)
GO
To fetch the record:
SELECT * FROM Tb_Table1

GO
Result:
ID	BitColumn

10 1
11 0
Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 1/13/2011 [Member] Starter | Points: 25

Up
0
Down
thanks for your response,

create table permit(empid varchar(35)primary key,empname varchar(35),active bit DEFAULT 1)

insert into permit values('e001','aa'')
insert into permit values('e002','bb',0)
insert into permit values('e003','cc',0)

i tried, but got error

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

please correct it.

thanks

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 1/13/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
hi,

You have not given the number of values that shld be passed in the first statement... thts the issue

insert into permit values('e001','aa',1)
insert into permit values('e002','bb',0)
insert into permit values('e003','cc',0)


Thanks
Karthik
www.f5Debug.net

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 1/13/2011 [Member] Starter | Points: 25

Up
0
Down

please refer Mr. pandianS code,

To Insert record:

INSERT Tb_Table1(ID) VALUES(10)

GO

INSERT Tb_Table1 VALUES(11,0)

GO

i followed that code, its right or wrong.

thanks

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 1/13/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
OK Dn2010,

insert into permit(empid,empname) values('e001','aa')
insert into permit values('e002','bb',0)
insert into permit values('e003','cc',0)

If you are not gng to pass the default then u need to specify the columns names as shows in the first statement.

Try it

Thanks
Karthik
www.f5Debug.net

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Suresh.mekkattil on: 1/13/2011 [Member] Bronze | Points: 25

Up
0
Down
CREATE TABLE <tablename>(
ID INT,
Bit BIT DEFAULT 1
)


-----
Suresh M

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response