| Author |
Topic |
|
vfactor
Starting Member
11 Posts |
 Posted - 2006-08-23 : 14:08:01
|
| Hi all,I have to transfer an Access Database to SQLserver. There're hundred of tables in the original DB which use True, False value. Those will be translated to -1, 0 in SQLserver. I need them to be 0 , 1 instead (-1=>1).Is there a way to tell SQLServer to process this modification during the transfer or is there a store procedure which can go through all tables and modify those values ?Any help will be appreciate..Thank you all |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-08-23 : 14:15:57
|
the column type is a 'bit' which by nature means -1 (true) or 0 (false).Why do you need them to be '1' instead of '-1'?? what difference does it make?If you are adding or something, then you can do the following:select bitSum = sum(case when yourCol=-1 then 1 else 0 end) from yourTableCorey Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."  |
 |
|
|
vfactor
Starting Member
11 Posts |
Posted - 2006-08-23 : 14:26:48
|
| uhmm it's long to explain, but I need -1 to be written as 1 in SQLserver.....on a multiple tables... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:28:11
|
quote:
Originally posted by Seventhnight
the column type is a 'bit' which by nature means -1 (true) or 0 (false).
I'm confused. According to BOL:quote:
bitInteger data type 1, 0, or NULL.
Or is the bit data type different in Access?Tara Kizer |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-08-23 : 14:32:14
|
Sorry... I wrote it backwards...  Access is -1, 0MS SQL is 1, 0point stands as why does it matter?quote:
uhmm it's long to explain
is not proof that its what you need to do. Only that it is what you think you want to do.Can you give us an example (code example) where this will make a difference?CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:46:32
|
I personally would want it to be 1 for true and 0 for false. If the import of the database to SQL Server doesn't put 1 for true, then I'd go back and fix it with update statements. -1 for true doesn't make sense to me. I wasn't brought up that way.  Tara Kizer |
|
|
|
vfactor
Starting Member
11 Posts |
Posted - 2006-08-23 : 16:03:10
|
| yes and the actual software code, there're a lot of changes where the coders used = 1 to validate stuff...-1 will return an error... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-24 : 02:50:15
|
| In SQL Server any value that is not exactly 0, convert to 1 when using BIT datatype. Only 0 becomes 0.Such as -1, when you import it, SQL converts automatically to 1.Peter LarssonHelsingborg, Sweden |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-24 : 12:37:50
|
And even if Peter's wrong, and it DID import as -1, why couldn't you just update afterwards to fix it? Import the data from Access to SQL Server, and then:UPDATE YourTable SET YourField = 1 WHERE YourField = -1 Ken |
|
|
|
|
Access to SQLServer