Extact datatype from a column value varchar SQL

Question

I have two tables:

table1{
   id: int,
   desc: varchar(50),
   type: varchar(50)
}

table2{
   id(tableX): varchar(20),
   id2(table1): int,
   value: sql_variant
}

Table1 have this row:

id	desc	type
1	Name	varchar(20)
2	Birthday	datetime

I want to insert values into table2 using the type defined on table1.

Something like this:

insert into table1 (id,id2,value) values('test',1,cast('John' as (select type from table1 where id = 1)))

Desired table2:

id	id2	Value
000000x	1	john
000000x	2	12/03/1960

I'm looking for the SQL equivalent of this C# code:

Type type = Type.GetType("System.Int32");
object value = 12;
Console.WriteLine(Convert.ChangeType(value, type));

is it possible to do this? is there another way?

Thanks.

Answer 1

You cannot use the CAST function dynamically. You could wrap the cast for each datatype inside CASE but the return datatype will be the one with highest priority and, for example, "John" cannot be converted to a date.

In theory, you could do this and it should work:

SELECT CASE type
       WHEN 'varchar(20)' THEN CAST(CAST('x' AS varchar(20)) AS sql_variant)
       WHEN 'datetime'    THEN CAST(CAST('x' AS datetime   ) AS sql_variant)
       WHEN ...
END

Here is a little proof of concept.