MS SQL Server question. Conditional insert

[Cogman]

Here is the setup, I'm transferring data from one table to another (Oh the fun!) One of the columns in the new table depend on the data in some of the other columns. So here is (essentially) what I'm trying to do.

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, (ColumnD > ColumnE) from tableB;

Obviously this is incorrect syntax. Is it possible to do something like this without having a huge if statement that forces me to essentially duplicate the code except for a 0 or 1 in Column Cs place? (Hope that makes sense)

 

[Train]

What type is column C ?

If you mean D "greater than" E, then there are several ways to do it. Heres a quick example.

Lets assume C is a bit column.

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, 1 from tableB where ColumnD > ColumnE;

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, 0 from tableB where ColumnD <= ColumnE;

************************
If you wanted to do it in single statement you could do somethng like

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, IF ColumnD > ColumnE 1 ELSE 0 from tableB;

But that syntax may be a tad off, I usually avoid IF statements in SQL

 

[Cogman]

What type is column C ?

If you mean D "greater than" E, then there are several ways to do it. Heres a quick example.

Lets assume C is a bit column.

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, 1 from tableB where ColumnD > ColumnE;

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, 0 from tableB where ColumnD <= ColumnE;

************************
If you wanted to do it in single statement you could do somethng like

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, IF ColumnD > ColumnE 1 ELSE 0 from tableB where;

But that syntax may be a tad off, I usually avoid IF statements in SQL

If does not work. Case is what I was looking for.

Here is an example of what the syntax ended up being

insert into tableA (ColumnA, ColumnB, ColumnC)
Select ColumnD, ColumnE, case when ColumnD > ColumnE then 1 else 0 end from tableB;

The type was a bit, (though, this is useful for other types)

 

[Train]

It would be nice if the bit data type in SQL were a true boolean, then your original statement would work. Unfortunately ISO-9* SQL is pretty antiquated.

But then again, the fact that it can be null as a bit makes this upgrade impossible. And adding a boolean type would probably be confusing. You can achive what is essentially a boolean field by using a bit with a Non-Null constraint