DEFAULT Values - DDL Statements - Oracle to SQL Server Migration

The DEFAULT clause allows you to specify the default value for a column. The default value is assigned if you omit the column or specify DEFAULT keyword in a INSERT statement.

DEFAULT Clause Conversion Details

The DEFAULT clause in CREATE TABLE and ALTER TABLE statements in Oracle and SQL Server may use a different syntax and require conversion.

ALTER TABLE - Add DEFAULT to Existing Table

In Oracle, you can use ALTER TABLE statement with MODIFY clause to add the default value for a column in an existing table:

Oracle:

  -- Create table without DEFAULT 
  CREATE TABLE states
  (
     abbr CHAR(2),
     name VARCHAR2(90)
  );
 
  -- Add DEFAULT for 'abbr' column
  ALTER TABLE states MODIFY (abbr DEFAULT '--');
 
  -- Insert a row
  INSERT INTO states(name) VALUES ('Unknown');

In SQL Server, you can also use ALTER TABLE statement, but with ADD DEFAULT clause:

SQL Server:

  -- Create table without DEFAULT 
  CREATE TABLE states
  (
     abbr CHAR(2),
     name VARCHAR(90)
  );
 
  -- Add DEFAULT for 'abbr' column
  ALTER TABLE states ADD DEFAULT '--' FOR abbr;
 
  -- Insert a row
  INSERT INTO states(name) VALUES ('Unknown');