what is the best way to rewite a oracle sql query to sql server 2012

  • I need to rewrite the attached code what is the best and quickest way to do so?

    thanks

  • The code you attached is just creating some tables. The syntax is very similar, remove the double quotes, adjust the data types (varchar2 to varchar, timestamp to datetime, number to numeric or int/bigint) and adjust the physical specifications according to the new environment (or remove them completely).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • STILL GETTING SOME ERROR

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 29

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 52

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 75

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 106

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 147

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 185

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 209

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 245

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 268

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 291

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 324

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 356

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 379

    Incorrect syntax near 'ENABLE'.

    Msg 102, Level 15, State 1, Line 410

    Incorrect syntax near 'ENABLE'.

  • In SQL Server you don't have to enable constraints. They're enabled by default.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Msg 1018, Level 15, State 1, Line 12

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 37

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 60

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 86

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 122

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 171

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 194

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 220

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 253

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 276

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 303

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 342

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 364

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 389

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 422

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

  • This is an attached update of my changes and still having for error can someone help

    pls ?

    Msg 1018, Level 15, State 1, Line 12

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 37

    Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    Msg 1018, Level 15, State 1, Line 60

  • Here's a few notes on the conversion process, and a sample of the first table converted.

    The double quotes are fine, as long as "SET QUOTED_IDENTIFIER ON" is active, which it always should be.

    NOT NULL ENABLE becomes NOT NULL

    !You need to explicitly specify NULL if that's what you want!

    VARCHAR2 becomes nvarchar; review to see if you can use varchar instead and cut the column size in half.

    TIMESTAMP(6) becomes datetime2

    "USING INDEX": PCTFREE 10 becomes WTIH ( FILLFACTOR = 90 ). Most of the rest of the values either don't apply in SQL Server, and a couple I'm not 100% sure what they mean.

    TABLESPACE "name" becomes ON [filegroupname] after the index created

    Foreign key specification is the same except remove "ENABLE".

    I don't know that SUPPLEMENTAL LOG DATA means, so can't relate it to SQL.

    SET ANSI_NULLS ON;

    SET ANSI_PADDING ON;

    SET ANSI_WARNINGS ON;

    SET ARITHABORT ON;

    SET CONCAT_NULL_YIELDS_NULL ON;

    SET NUMERIC_ROUNDABORT OFF;

    SET QUOTED_IDENTIFIER ON;

    GO

    CREATE TABLE "PREFCOMM"."T_SERVICE_EVENT"

    ("SERVICE_EVENT_CD" nvarchar(5) NOT NULL,

    "DEFAULT_CONTACT_METHOD_CD" nvarchar(5) NOT NULL,

    "SERVICE_EVENT_DESC" nvarchar(200) NULL,

    "SERVICE_EVENT_NAME" nvarchar(20) NULL,

    "RECORD_STAT" nvarchar(1) NOT NULL,

    "CREATED_BY" nvarchar(100) NOT NULL,

    "CREATED_DT" datetime2 NOT NULL,

    "MODIFIED_BY" nvarchar(100) NULL,

    "MODIFIED_DT" datetime2 NULL,

    CONSTRAINT "PK_SERVICE_EVENT" PRIMARY KEY ("SERVICE_EVENT_CD") WITH ( FILLFACTOR = 90 ) ON "PREFCOMM_DATA",

    CONSTRAINT "FK_SERVICE_EVENT" FOREIGN KEY ("DEFAULT_CONTACT_METHOD_CD") REFERENCES "PREFCOMM"."T_CONTACT_METHOD" ("CONTACT_METHOD_CD")

    )

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Luis Cazares (12/11/2015)


    .. adjust the data types (varchar2 to varchar, ...

    varchar2 is nvarchar.

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply