{"id":1049,"date":"2015-11-20T12:36:43","date_gmt":"2015-11-20T12:36:43","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=1049"},"modified":"2015-11-20T12:50:12","modified_gmt":"2015-11-20T12:50:12","slug":"data-migrations-scripts","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/data-migrations-scripts\/","title":{"rendered":"Data Migrations Scripts"},"content":{"rendered":"<p><strong>Modifying Column Type in Table<\/strong><br \/>\n<strong>Method 1<\/strong> : <\/p>\n<ol>\n<li>Add a Copy of the Column appending to some identifier to say its a copy i.e <em>_CP<\/em><\/li>\n<li>Update the New Column to the Column whose datatype is to be changed<\/li>\n<li>Drop the Old Column<\/li>\n<li>Rename the New Column to Old Column Name<\/li>\n<\/ol>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nALTER TABLE TB1 ADD COL_CP VARCHAR2(10);\r\n\r\nUPDATE TB1 SET COL_CP = COL1;\r\n\r\nALTER TABLE TB1 DROP COLUMN COL1;\r\n\r\nALTER TABLE TB1 RENAME COLUMN COL_CP to COL1;\r\n<\/pre>\n<p><strong>In case you are upgrading from Varchar2(250) to Varchar2(4000) the above is not required<\/strong><\/p>\n<p><strong>Method 2<\/strong> : <\/p>\n<ol>\n<li>Create a New table(TB2) with the same Columns and Type<\/li>\n<li>Insert the Values into TB2<\/li>\n<li>Truncate or Drop Table TB1 <\/li>\n<li>Run the Changes such as New DDL Script<\/li>\n<li>Insert into new Table TB1 from TB2<\/li>\n<li>Drop Table TB2<\/li>\n<\/ol>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE TB1(COL1 NUMBER);\r\n\r\nINSERT INTO TB1 VALUES(10);\r\n\r\nCREATE TABLE TB2 AS\r\nSELECT * FROM TB1;\r\n\r\nDROP TABLE TB1;\r\n\r\n.\r\n.\r\nNew table Creation Script with New Column Types\r\n.\r\n.\r\n.\r\n\r\nINSERT INTO TB1\r\nSELECT * FROM TB2;\r\n\r\nDROP TABLE TB2;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Modifying Column Type in Table Method 1 : Add a Copy of the Column appending to some identifier to say its a copy i.e _CP Update the New Column to the Column whose datatype is to be changed Drop the Old Column Rename the New Column to Old Column Name ALTER TABLE TB1 ADD COL_CP&hellip; <a href=\"https:\/\/codethataint.com\/blog\/data-migrations-scripts\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[170],"tags":[],"class_list":["post-1049","post","type-post","status-publish","format-standard","hentry","category-data-migration"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1049","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=1049"}],"version-history":[{"count":4,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1049\/revisions"}],"predecessor-version":[{"id":1053,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1049\/revisions\/1053"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=1049"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=1049"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=1049"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}