Anoj Anoj - 6 months ago 12
SQL Question

Copy just the Id of one table to another

I want to use the Primary key Id of table SourceTable to DestTable. SourceTable autogenerates the unique Id and I have to insert this Id to DestTable. System.DateTime.Now(I am working with Asp.net C#) value is the only unique data that can refer to this Id. Below is my query:

string @Date = ("Select Id from SourceTable Where Date=' " + System.DateTime.Now + " '");
string insertId = "insert into DestTable (Id) SELECT Id FROM SourceTable WHERE Date= ' " + Date + " ' ";
SqlCommand comId = new SqlCommand(insertId, conn);


When I execute this command, my DestTable's Id column is empty. What is my solution?

Answer

Why would you use two queries for this? Just use:

insert into DestTable (d)
    select id
    from SourceTable
    where date = CURRENT_DATE;

Your code is probably not returning an answer because now() has a time component and that prevents the date from matching the constant.

Notes: CURRENT_DATE depends on the database. This might be trunc(sysdate) (Oracle), cast(getdate() as date) (SQL), or something else.

Comments