how to do this select query???

I have a table that looks something like this:

CREATE TABLE Oval_Import
(
IdNum INT NOT NULL PRIMARY KEY,
DOB datetime NOT NULL,
.
.
.
.
.
)

I'm trying to select all the records from the table (notice the DOB date field returns only the date part), but I ran into problems displaying the rest of the fields after the DOB.

I tried a select query like this but it returned every column 'again' after the DOB:

select IdNum, convert(varchar,DOB,111), * from Oval_Import;

I don't want to explicitly select each individual column after that either because there are very many after the DOB.

So how can I select the rest of the fields with the * but excluding the IdNum and the DOB columns

Answer this question

how to do this select query???

junfanjohn

The INSERT statement syntax supported by SQL Server is the same as that of the ANSI SQL standards. You can only use the VALUES clause to specify the column values. There are extensions to the insert statement that allow you to do insert...Select or insert..exec for example. Perhaps you can do:

insert into Oval_Import (IdNum, DOB)
select 226882 as IdNum, '1982/1/9' as DOB

But note that the columns between the select and insert statement list is still by position.
HoundOfHell
Ok, how about when I do an insert.

I can perform a query like this in sql server:

insert into Oval_Import (IdNum, DOB) values (226882, '1982/1/9');

but I can't do the following, it gives me a "Incorrect syntax near the keyword 'set'." error:

insert into Oval_Import set IdNum=226882, DOB='1982/1/9';

I know in mysql you can perform the latter query for insertion, but how can you do something similar in sql server I need an insertion query where I can explicitly see which columns are being assigned to which values because my table have many columns.
AustinNetherwood
It's a good practice to explicitly define each column in the select statement. Also, when you do special conversion on a column, you are essentially creating a new column. It's not possible to remove the original column from the select if you don't explicitly do so.
how to do this select query???

Answer this question