with cols as (
select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,
case
when t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’
end oracle_type
from sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_id
join sys.tables st on st.object_id=ac.object_id
join sys.schemas s on s.schema_id=st.schema_id
where st.name!=’sysdiagrams’
),
grp as
(select upper(table_name) table_name, col= upper(STUFF (
(SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”) from cols t1 where t1.table_name=t2.table_name for XML PATH(”)),1,1,”)) from cols t2
group by table_name)
select ‘LOAD DATA
INFILE “‘+table_name +’.csv” “str ”EOL””
TRUNCATE
INTO TABLE ‘ +table_name +’
FIELDS CSV WITH EMBEDDED
DATE FORMAT “YYYY-MM-DD HH24:MI:SS.FFF”
(‘+col+’)’
from grp