Database

Re: SQL Server 2010, smalldatetime insert

1-Jun-21 21:49

jkirkerx wrote:
INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 )

I guess the VALUES list does not correspond the fields list, so the value form ADD_DATE seems to be '0' rather than '1900-01-01 00:00:00.000'.

jkirkerx2-Jun-21 8:47

jkirkerx

2-Jun-21 8:47

I thought about what you both said, and would like to say thanks and show gratitude as well.

I went back and keep the Select statement raw, took the convert out.
The dumped my CRUD function to add the record, and converted the calling function to SQL.
Then used PHP to convert the raw date object I picked up to a string, and declared it in SQL.

I suppose I could go back to my add CRUD function and do the conversion there instead

$result2 = sqlsrv_query($conn, $query2) or die(" setDbProjectCost " . LINE . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
    $row2 = sqlsrv_fetch_array($result2);
    $addDateString = $row2[13]->format('Y-m-d H:i:s') . '.000';

    $query3 = "
    DECLARE @addDate AS VARCHAR(33) = '$addDateString';
    INSERT INTO [proj_cost] 
    VALUES ... @addDate

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Get columns name

_Flaviu27-May-21 23:55

27-May-21 23:55

If I run a SQL statement, like this:

SQL

SELECT * FROM my_table

is there any SQL statement / trick to find the name of every column from '*' ?

Because if I write:

SQL

SELECT id, name, age FROM my_table

yes, I could extract the columns name, by text analyze. But how about '*' case ?

Victor Nijegorodov28-May-21 0:22

How can I get column names from a table in SQL Server? - Stack Overflow
sql - How can I get column names from a table in Oracle? - Stack Overflow
information schema - How to find all the tables in MySQL with specific column names in them? - Stack Overflow
postgresql - List all columns for a specified table - Database Administrators Stack Exchange

28-May-21 0:22

_Flaviu3-Jun-21 5:57

3-Jun-21 5:57

Yes, I know that, but the question is, how can I know the columns name for the following SELECT (programmatically):

SQL

SELECT * FROM table1, table 2 WHERE table1.id = table2.id;

modified 3-Jun-21 12:17pm.

Victor Nijegorodov3-Jun-21 20:13

3-Jun-21 20:13

I'd just do it the same way as for a single table:
- obtain column names for table1;
- obtain column names for table2;
- ...

_Flaviu3-Jun-21 20:37

3-Jun-21 20:37

So, you are saying that I need to do a static analyze on SELECT text and see what columns I have, if I enumerate the columns in the SELECT statement, then I have it, if I don't (I have '*') then I have to query the tables names / columns name and will find them. I thought there is a SQL trick to find the columns name in any circumstances.

Victor Nijegorodov3-Jun-21 21:48

3-Jun-21 21:48

I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses

SQL

SELECT [TABLE_NAME]
      ,[COLUMN_NAME]
  FROM [INFORMATION_SCHEMA].[COLUMNS]
  where TABLE_NAME = <your_temp_view>

to return the recordset with the tables/columns names,
then delete the temp view.

PS: this should work with SQL Server 2008 and above.

_Flaviu3-Jun-21 23:33

3-Jun-21 23:33

Sound good ! Because this solution should be cross server platform, it is available on the other SQL servers, like Oracle, Informix, MySQL, and so on ?

Victor Nijegorodov4-Jun-21 0:55

4-Jun-21 0:55

You have to test it yourself! Just check out the links I gave you here

Gerry Schmitz4-Jun-21 7:02

Gerry Schmitz

4-Jun-21 7:02

If you load your query into a DataTable, you can get the column names from meta data.

datatable - How do I get column names to print in this C# program? - Stack Overflow

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

Victor Nijegorodov4-Jun-21 7:21

4-Jun-21 7:21

AFAIK, the OP develops in C++.
However, I may be wrong.

Data Access Using ADO.NET (C++/CLI) | Microsoft Docs

Gerry Schmitz4-Jun-21 7:54

Gerry Schmitz

4-Jun-21 7:54

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

Victor Nijegorodov4-Jun-21 8:02

4-Jun-21 8:02

But it is managed C++/CLI. Sigh | :sigh:

It has nothing to do with the native C++. Blush | :O

Gerry Schmitz4-Jun-21 9:01

Gerry Schmitz

4-Jun-21 9:01

Yeah, but ...

Using ADO.NET in MFC Projects

(And it's a "database" forum)

Before ADO.NET there was ADO, and MFC did ADO.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

modified 4-Jun-21 15:09pm.

Victor Nijegorodov4-Jun-21 9:47

4-Jun-21 9:47

Interesting idea. Thank you!
BTW, I used ADO in my big VS2010 project (C++/MFC with ADO with SQL Server) from 2009 to 2015, of course without any mixture with managed code! Wink | ;)

Mycroft Holmes4-Jun-21 12:43

Mycroft Holmes

4-Jun-21 12:43

Try this select * from table1 where 1=1 this should return an empty datatable with all the columns. Simply iterate the columns to get their names.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

mverbeke31-Aug-21 3:59

mverbeke

31-Aug-21 3:59

Try:

SELECT [name]. [column_id] AS [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id];

That will get the column names and ordinal position.

Richard Deeming31-Aug-21 4:50

Richard Deeming

31-Aug-21 4:50

I suggest you read the documentation:

sys.columns (Transact-SQL) - SQL Server | Microsoft Docs[^]:
Column IDs might not be sequential.

If a column has ever been dropped from the table, you will end up with gaps in the column ID sequence.

To get a true ordinal position, you'd need to use the ROW_NUMBER windowing function - for example:

SQL

SELECT [name], ROW_NUMBER() OVER (ORDER BY [column_id]) As [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

SQL statements

Erick Kaira27-May-21 8:09

Erick Kaira

27-May-21 8:09

Can anyone help me write the answers to this question in sql, I couldn't manage to upload, a full question, can anyone help with a simple demonstration on this question based on any assumption

I) Find the Employers name, address of employers and the number of
students sponsored by each employee.
iv)
Find the average fees of students sponsored by employer with number 02

Re: SQL statements

Victor Nijegorodov27-May-21 8:40