CODEWITHSUNDEEP
sqlsql-server
Jammy
Jammy

Reputation: 37

Date and value conversion

I'm Inserting the ID's from table2 which is not exist in the table1. Table1 have perfect datatype in date(datetime), Gen(varchar(1)) but Table2 has different datatype for the same column date(varchar(255)), Gen(float) 1-M,2-F. I share the problem in sample set.

Table1

  ID  date                  Gen
    193 1996-03-26 00:00:00 M
    446 1997-09-20 00:00:00 F
    689 1997-02-21 00:00:00 F
    612 1993-10-19 00:00:00 M

Table2

ID  date                    Gen
123 1993-03-02 00:00:00     1
456 2019-10-19 11:50:13.913 2
689 1997-02-21 00:00:00     2
789 2019-11-04 08:06:36.71  1
012 2000-10-02 07:11:19     1

I need to append the new ID's in table1. while using the insert query how can I convert the date and Gen variable like table1 format.

Result: Table1

  ID  date                  Gen
    193 1996-03-26 00:00:00 M
    446 1997-09-20 00:00:00 F
    689 1997-02-21 00:00:00 F
    612 1993-10-19 00:00:00 M
    123 1993-03-02 00:00:00 M
    456 2019-10-19 00:00:00 F
    789 2019-11-04 00:00:00 M
    012 2000-10-02 00:00:00 M

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want to insert the rows in table2 that are not in table1, you can use insert with filtering logic:

insert into table1 (id, date, gen)
    select t2.id, t2.date, (case when gen = 1 then 'M' else 'F' end)
    from table2 t2
    where not exists (select 1 from table1 t1 where t2.id = t1.id);

Upvotes: 1

Related Questions