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<\/p>\n","protected":false},"excerpt":{"rendered":"
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,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere 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 t2group by table_name)select ‘LOAD DATAINFILE […]<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3],"tags":[],"class_list":["post-160","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql","category-oracle"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=160"}],"version-history":[{"count":4,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":170,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions\/170"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}