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.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2002-02-26 : 05:21:22
|
| Hiya,I imported an Access database into SQL Server. Made all the primary keys and such, but all my Yes/No fields are changed to bit field which is OK, however the defaults are set to either 0 or 1. So making new records return errors because there's this 0 or 1 missing.I can change the design of the table, but all my 70 tables need to be changed. Worse, after testing I have to do it all over for the production data.So my question: How can I change all my bit fields in my tables so that defaults are either 0 or 1 by default. (I believe there's an query for this..)Thx.Henri~~Guilt is like a bag of bricks. All you gotta do is set it down... |
|
|
samrat
Yak Posting Veteran
94 Posts |
Posted - 2002-02-26 : 07:55:56
|
| Didn't u say that the default value is already set to 0 or 1?? or am i not getting your question correctly???Cheers,SamratEdited by - samrat on 02/26/2002 07:56:24 |
 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-02-26 : 10:01:36
|
| Sorry, my fault, The defaults are empty. So making a new record without explicit giving it a 0 or a 1 are returned with an error.Every table has a field which is called 'Active'. When an user wants to delete a record Active is set to 0 (instead of deleting it). I don't want manually change all tables and set the default of the field Active to 1. I want to run a query to set the default value of the active field to 1 in every table. (Beside the 70 tables I have 8 databases...).Thx,Henri~~~Guilt is like a bag of bricks. All you gotta do is set it down... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-26 : 10:20:36
|
| If you want it to default to a value, you need to add the default to each affected table and column. You can do this using Enterprise Manager or T-SQL (ALTER TABLE statement). For the existing data, you'll need to run UPDATE statements on each table to change the value as needed. There's no other way. |
|
|
|
|
|
|
Changes all bit defaults at once