• State Suggested Answer
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 16 subscribers
  • Views 112 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Best Practices

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

edmondx0001

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.

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    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;
Reply
  • 0
    Certified Lead Developer

    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;
Children
No Data

© 2024 Appian. All rights reserved.