question

MrSQLDBA avatar image
MrSQLDBA asked

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

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 [link text][1] [1]: /storage/temp/ 3104-script.txt
sqlsql-serversql-server-2012
script.txt (21.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
Microsoft have published a tool called *Microsoft SQL Server Migration Assistant* that is supposed to do SQL statement conversion in addition to full-on database migrations with data and testing - [ http://blogs.msdn.com/b/ssma/archive/2014/08/21/latest-update-microsoft-sql-server-migration-assistant-ssma-v6-0-is-now-available.aspx][1] It looks like 6.0 (August 2014) is the most recent version of the tool - [ https://www.microsoft.com/en-us/download/details.aspx?id=43689][2] [1]: http://blogs.msdn.com/b/ssma/archive/2014/08/21/latest-update-microsoft-sql-server-migration-assistant-ssma-v6-0-is-now-available.aspx [2]: https://www.microsoft.com/en-us/download/details.aspx?id=43689
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MrSQLDBA avatar image
MrSQLDBA answered
This the Answer who works for me. http://www.sqlservercentral.com/Forums/Topic1744704-3077-1.aspx#bm1744717 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") )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.