What is the best practice to deploy update in a cloud database table

Question

Hello all, currently I have 3 org, dev, QA, and prod. I have made new inserts in a cloud data base table in the dev environment. Given that the same table in the QA org have the same 14 entries as the table in dev org, what would be the best practice to deploy the update from said table from the dev to QA org. Any help would be appreciated.

Srinivas Thota · Answer

Hi edmondx0001, When you deal with database scripts either in manual or automation scripts, I recommend using idempotent SQL Scripts as per Database best practices for deployment, which can be call as rerunnable script. These scripts can be used for creating table, alter, create view and other scripts use of which of information_schema. This will use be useful when you use third party automation pipelines. As per your problem statement i recommend you use replace keyword instead of insert. below is example of idempotent sql script. 
 IF ( 
 ( 
 SELECT CASE --return 0 if not nullable 1 if nullable 
 ( --return null if it doesn't exist 
 SELECT IS_NULLABLE 
 FROM INFORMATION_SCHEMA . COLUMNS 
 WHERE TABLE_SCHEMA = 'dbo' 
 AND TABLE_NAME = 'prices' 
 AND COLUMN_NAME = 'Edition_id' 
 ) WHEN 'NO' THEN 0 
 WHEN 'YES' THEN 1 ELSE - 1 END 
 ) = 1 
 ) 
 BEGIN 
 IF NOT EXISTS ( SELECT 1 FROM prices WHERE Edition_id IS NULL ) 
 ALTER TABLE dbo . prices ALTER COLUMN Edition_id INT NOT NULL ; 
 END ;

What is the best practice to deploy update in a cloud database table

Top Replies