2

I have two tables.

  1. NewTransaction_tb
  2. OldTransaction_tb

I want to move the records from old to new table including date. But the OldTransaction_tb it doesn't have the Date column.

This is what I am trying.

For example

DECLARE @VarDate Datetime = CONVERT(datetime,GETDATE(),102)

INSERT INTO HQMatajer.dbo.NewTransaction_tb
    SELECT
        Name, class, Qualification, @VarDate  //this @VarDate is not in OldTransaction_tb
    FROM 
        HQMatajer.dbo.OldTransaction_tb

What is the solution for this scenario? Thanks,

Improve this question
1
  • what is the result of your query? Commented Jan 17, 2017 at 6:18

4 Answers 4

Reset to default
3

You don't have to declare a variable for doing this, you can directly convert the string from the old table and insert to the new one.

INSERT into HQMatajer.dbo.NewTransaction_tb
SELECT Name,class,Qualification,CONVERT(datetime,GETDATE(),102)
FROM HQMatajer.dbo.OldTransaction_tb
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, its second time you are answering me.
True, the direct approach does work. However, there can be a difference, when it comes to long tables. The original approach (there should have been a separate SET command after the DECLARE) would result in identical values for all rows, whereas in your solution you will have slight time differences between them.
1

You can use directly Date in select column

SELECT
Name,class,Qualification,CONVERT(datetime,GETDATE(),102)
Improve this answer

2 Comments

oh my god!... for this simple work I am thinking outside of box. Thanks bro
Happy to Help! :)
1

See, the answer to this can be what you wrote or what others suggested. The question is what you want in your result set. For example, if you are processing a data set all at once, say entire of OldTransaction table, and you want that all the rows being transferred to NewTransaction should have the same DateTime, then it is preferred to do it by first declaring a variable and then calling it.

This is better than using the function in a SELECT clause because the function is then called once for every row. So if you have a billion rows in OldTransaction table then the function will be called a billion times and you will have a small speed impact.

But if your require all rows to have the exact date time of insertion, in case your insert takes a prolonged time over an hour or so, then there is no choice but to use the function within the SELECT statement.

SELECT
Name, Class, Qualification, CONVERT(Datetime, GETDATE(), 102)
FROM HQMatajer.dbo.OldTransaction_tb
Improve this answer

Comments

0

Check this:

if table does not yet exist then use :

Select * 
Into NewTransaction_tb 
From 
    (select 
         * ,CONVERT(datetime, GETDATE(), 102) as Date 
     from 
         OldTransaction_tb) a

OR if table already exists, then use :

insert into NewTransaction_tb
    select 
        *, CONVERT(datetime, GETDATE(), 102) as Date  
    from 
        OldTransaction_tb
Improve this answer

1 Comment

It would be important to mention which approach works in what situation. The SELECT .. INTO .... only works if the target table does not yet exist - if it already exists, then you must use approach #2 ....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.