MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘sqlcmd’ Category

Convert to SQL Server?

with 2 comments

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table name to database_user. That’s what I’ve done in this example.

It’s also important to note that this example uses Microsoft SQL Server’s sqlcmd in batch mode. Naturally, it presumes that you’ve created a student user with a trivial password of student, and a studentdb schema. Also, that you’ve granted privileges so everything works (if you need help on that check my earlier post on how to setup a studentdb schema).

The following is an example of conditionally dropping and then creating a system_user table in an Oracle schema. It uses a CASCADE CONSTRAINTS clause to eliminate dependencies with foreign key values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

--  Create SYSTEM_USER table.
CREATE TABLE system_user
( system_user_id            NUMBER        CONSTRAINT system_user_pk PRIMARY KEY
, system_user_name          VARCHAR2(20)  CONSTRAINT system_user_nn1 NOT NULL
, system_user_group_id      NUMBER        CONSTRAINT system_user_nn2 NOT NULL
, system_user_type          NUMBER        CONSTRAINT system_user_nn3 NOT NULL
, first_name                VARCHAR2(20)
, middle_name               VARCHAR2(20)
, last_name                 VARCHAR2(20)
, created_by                NUMBER        CONSTRAINT system_user_nn4 NOT NULL
, creation_date             DATE          CONSTRAINT system_user_nn5 NOT NULL
, last_updated_by           NUMBER        CONSTRAINT system_user_nn6 NOT NULL
, last_update_date          DATE          CONSTRAINT system_user_nn7 NOT NULL
, CONSTRAINT system_user_fk1  FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, CONSTRAINT system_user_fk2  FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id));
 
-- Create SYSTEM_USER_S1 sequence with a start value of 1001.
CREATE SEQUENCE system_user_s1 START WITH 1001;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'COMMON_LOOKUP') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE common_lookup CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'COMMON_LOOKUP_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE common_lookup_s1';
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

--  Create COMMON_LOOKUP table.
CREATE TABLE common_lookup
( common_lookup_id            NUMBER
, common_lookup_context       VARCHAR2(30) CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR2(30) CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR2(30) CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  NUMBER       CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             NUMBER       CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT pk_c_lookup_1    PRIMARY KEY(common_lookup_id)
, CONSTRAINT fk_c_lookup_1    FOREIGN KEY(created_by) REFERENCES system_user(system_user_id)
, CONSTRAINT fk_c_lookup_2    FOREIGN KEY(last_updated_by) REFERENCES system_user(system_user_id));
 
-- Create a non-unique index on a single column.
CREATE INDEX common_lookup_n1
  ON common_lookup(common_lookup_context);
 
-- Create a unique index based on two columns.
CREATE UNIQUE INDEX common_lookup_u2
  ON common_lookup(common_lookup_context,common_lookup_type);
 
-- Create COMMON_LOOKUP_S1 sequence with a start value of 1001.
CREATE SEQUENCE common_lookup_s1 START WITH 1001;

You can do the same thing for a database_user table in Microsoft SQL Server with the following syntax. Unfortunately, there isn’t a CASCADE CONSTRAINTS clause that we can append in Microsoft SQL Server. The script uses a dynamic SQL statement with a Common Table Expression (CTE) to generate a list of ALTER statements that drop foreign key constraints in the schema.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

/* Drop all foreign keys. */
USE studentdb;
 
/* Create a session variable to hold a command list. */
SELECT 'Create a session variable.' AS "Statement";
DECLARE @sql NVARCHAR(MAX) = N'';
 
/* Generate the command list to drop foreign key constraints. */
SELECT 'Generate dynamic SQL statements.' AS "Statement";
;WITH x AS
(SELECT N'ALTER TABLE ' +
          OBJECT_SCHEMA_NAME(parent_object_id) + N'.' +
          OBJECT_NAME(parent_object_id) + N' ' +
        N'DROP CONSTRAINT ' + name + N';' AS sqlstmt
 FROM     sys.foreign_keys)
SELECT @sql += sqlstmt FROM x;
 
/* Call the dynamically generated statements. */
SELECT 'Execute dynamic SQL statements.' AS "Statement";
EXEC sp_executesql @sql;
 
/* Conditionally drop tables. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.database_user','U') IS NOT NULL
  DROP TABLE studentdb.database_user;
 
/* Create a table with self-referencing foreign key constraints. */
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.database_user
( database_user_id        INT          NOT NULL IDENTITY(1,1) CONSTRAINT database_user_pk PRIMARY KEY
, database_user_name      VARCHAR(20)  NOT NULL
, database_user_group_id  INT          NOT NULL
, database_user_type      INT          NOT NULL
, first_name              VARCHAR(20)
, middle_name             VARCHAR(20)
, last_name               VARCHAR(20)
, created_by              INT          NOT NULL
, creation_date           DATE         NOT NULL
, last_updated_by         INT          NOT NULL
, last_update_date        DATE         NOT NULL
, CONSTRAINT database_user_fk1 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT database_user_fk2 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id));
 
/* Conditionally drop common_lookup table. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.common_lookup','U') IS NOT NULL
  DROP TABLE studentdb.common_lookup;
 
/* Create a table with external referencing foreign key constraints. */  
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.common_lookup
( common_lookup_id            INT          NOT NULL  IDENTITY(1,1) CONSTRAINT common_lookup_pk PRIMARY KEY
, common_lookup_context       VARCHAR(30)  CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR(30)  CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR(30)  CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  INT          CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             INT          CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT common_lookup_fk1 FOREIGN KEY(created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT common_lookup_fk2 FOREIGN KEY(last_updated_by)
  REFERENCES studentdb.database_user (database_user_id));

You can run it from a file by calling the sqlcmd utility. You’ll need to know several things to run it. First, you need to know your database instance. You can capture that from a query against the data dictionary or catalog. Just run the following from inside the Microsoft SQL Server Management Studio (SSMS):

SELECT @@SERVERNAME;

In my case, it shows the following, which is the machine’s hostname a backslash and SQLEXPRESS:

MCLAUGHLINSQL\SQLEXPRESS

The script uses sqltest.sql as a file name, and you can call it from the Windows shell environment like this:

sqlcmd -S MCLAUGHLINSQL\SQLEXPRESS -U student -P student -i C:\Data\MicrosoftSQL\sqltest.sql -o C:\Data\Microsoft\sqltest.out

As always, I hope this helps.

Written by maclochlainn

January 15th, 2015 at 7:18 pm

Posted in Microsoft SQL Server,Oracle,sql,sqlcmd,T-SQL

Tagged with ,