Is there an elegant way to Invert a Bit value in an SQL insert Statement?

sql sql-server bit-manipulation
Molloch picture Molloch · Jul 24, 2010 · Viewed 27.9k times · Source

I'm converting some data in SQL Server:

INSERT INTO MYTABLE (AllowEdit)
(Select PreventEdit from SOURCETABLE)

so I need to inverse the bit value from source table. I expected NOT to work, as this is how I would do it in code, but it doesn't. The most elegant way I can think of is:

INSERT INTO MYTABLE (AllowEdit)
(Select ABS(PreventEdit -1) from SOURCETABLE)

Is there a more standard way to do it?

Answer

driis picture driis · Jul 24, 2010

I did not test this myself, but you should be able to use the bitwise negation operator, ~ on a bit:

INSERT INTO MYTABLE (AllowEdit) 
(SELECT ~PreventEdit FROM SourceTable)

Related questions

How do I flip a bit in SQL Server?

I'm trying to perform a bitwise NOT in SQL Server. I'd like to do something like this: update foo set Sync = NOT @IsNew Note: I started writing this and found out the answer to my own question before I finished. …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More