Click here to Skip to main content
14,635,061 members
Home » Discussions » Database
   

Database

 
AnswerRe: Copy structure of all the tables of one database to another server Pin
phil.o31-Jan-20 5:12
mvephil.o31-Jan-20 5:12 
AnswerRe: Copy structure of all the tables of one database to another server Pin
Eddy Vluggen3-Feb-20 3:01
professionalEddy Vluggen3-Feb-20 3:01 
Questionintegrity constraint Pin
Tara1129-Jan-20 10:36
MemberTara1129-Jan-20 10:36 
AnswerRe: integrity constraint Pin
Mycroft Holmes29-Jan-20 10:56
professionalMycroft Holmes29-Jan-20 10:56 
GeneralRe: integrity constraint Pin
Tara1129-Jan-20 11:05
MemberTara1129-Jan-20 11:05 
GeneralRe: integrity constraint Pin
Mycroft Holmes29-Jan-20 11:21
professionalMycroft Holmes29-Jan-20 11:21 
AnswerRe: integrity constraint Pin
Wendelius2-Feb-20 9:01
mveWendelius2-Feb-20 9:01 
QuestionOracle SQL query to MS-SQL Pin
Member 951930620-Jan-20 22:37
MemberMember 951930620-Jan-20 22:37 
Hi
I have to migrate a database from Oracle to MS-SQL. The database migration went well, but i have a query that gives me trouble. I have tried to convert it using this on-line tool - http://www.sqlines.com/online, but it is not working since the Oracle alias seems to be a problem for MS-SQL.
Is there any way to use a similar syntax in MS-SQL?

The original Oracle SQL:

UPDATE folk.individ c
   SET (c.stat, 
		c.statusdto, 
		c.pnrgaeld,
		c.koen, 
		c.fornvnmrk, 
		c.mellemnvnmrk,
		c.efternvnmrk,
		c.nvndto, 
		c.fornvn_solo,
		c.fornvn, 
		c.mellemnvn,
		c.efternvn,
		c.adr_fornvn,
		c.adr_efternvn,
		stilling,
		z_stilling,
		c.z_fornvn,
		c.z_mellemnvn,
		c.z_efternvn,
		c.z_adr_fornvn, 
		c.z_adr_efternvn, 
		c.z_fornvn_solo,
		c.haenstart_umrk_navne,
		c.stillingdto,
		c.mynkod_ctnavn,
		c.myntxt_ctnavn,
		c.indrap,
		c.adrnvndto,
		c.soegnvn,
		c.soegnvndto
		) =
(SELECT l.status,
		TO_DATE(DECODE(SUBSTR(l.statushaenstart,7,2),'00',NULL,
	       SUBSTR(l.statushaenstart,1,8)),'YYYYMMDD'), 
	     l.pnrgaeld,
		 l.koen, 
		 a.fornvn_mrk, 
		 a.melnvn_mrk,
		 a.efternvn_mrk,
		 TO_DATE(DECODE(SUBSTR(a.nvnhaenstart,7,2),'00',NULL,
	       SUBSTR(a.nvnhaenstart,1,8)),'YYYYMMDD'),
		Upper(a.fornvn),	-- c.fornvn_solo
		substr(decode(a.melnvn, null, upper(a.fornvn), upper(a.fornvn)|| ' ' || upper(a.melnvn) ),1,50),   -- skal være som før: fornavn indh. også mlnavne
		upper(a.melnvn), 
		upper(a.efternvn),
		upper(LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1))),  --adr_fornvn
        upper(SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1)), --adr_efternavn
        upper(l.stilling),
        l.stilling,
        substr(decode(a.melnvn, null, a.fornvn, a.fornvn || ' ' || a.melnvn),1,50),   -- z_fornvn,  fornavn indh. også mlnavne
		a.melnvn,  -- z_mellemnvn
		a.efternvn,
		LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1)),  --z_adr_fornvn
        SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1), -- z_adr_eftermnvn
        a.fornvn,		-- z_fornvn_solo
        a.haenstart_umrk_navne,	-- anvendelse ukendt
        null, --stillingdto udgået
        null, --mynkod_ctnavn  udgået
        null, --myntxt_ctnavn  udgået
        null, --indrap  udgået
        null, -- adrnvndto  udgået
        null,  --soegnvn udgået
        null	--soegnvndto udgået
   FROM folk.lperson2010 l, folk.laktnvnopl2010 a
 WHERE  l.pnr = c.pnr
   AND  l.pnr = a.pnr	
   AND  l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
	  	where  l.lseq BETWEEN 1 AND 6000 );


The code after conversion parsing it through http://www.sqlines.com/online:

UPDATE folk.individ c
   SET (c.stat, 
		c.statusdto, 
		c.pnrgaeld,
		c.koen, 
		c.fornvnmrk, 
		c.mellemnvnmrk,
		c.efternvnmrk,
		c.nvndto, 
		c.fornvn_solo,
		c.fornvn, 
		c.mellemnvn,
		c.efternvn,
		c.adr_fornvn,
		c.adr_efternvn,
		stilling,
		z_stilling,
		c.z_fornvn,
		c.z_mellemnvn,
		c.z_efternvn,
		c.z_adr_fornvn, 
		c.z_adr_efternvn, 
		c.z_fornvn_solo,
		c.haenstart_umrk_navne,
		c.stillingdto,
		c.mynkod_ctnavn,
		c.myntxt_ctnavn,
		c.indrap,
		c.adrnvndto,
		c.soegnvn,
		c.soegnvndto
		) =
(SELECT l.status,
		CONVERT(DATETIME, CASE SUBSTRING(l.statushaenstart,7,2) WHEN '00' THEN NULL
	        ELSE SUBSTRING(l.statushaenstart,1,8) END,'YYYYMMDD'), 
	     l.pnrgaeld,
		 l.koen, 
		 a.fornvn_mrk, 
		 a.melnvn_mrk,
		 a.efternvn_mrk,
		 CONVERT(DATETIME, CASE SUBSTRING(a.nvnhaenstart,7,2) WHEN '00' THEN NULL
	        ELSE SUBSTRING(a.nvnhaenstart,1,8) END,'YYYYMMDD'),
		Upper(a.fornvn),	-- c.fornvn_solo
		substring(case   when a.melnvn is null then  upper(a.fornvn) isnull( else upper(a.fornvn), '')+ ' ' + isnull(upper(a.melnvn), '')  end,1,50),   -- skal være som før: fornavn indh. også mlnavne
		upper(a.melnvn), 
		upper(a.efternvn),
		upper(LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn)+1, LEN(a.adrnvn)))),  --adr_fornvn
        upper(SUBSTRING(a.adrnvn,1,CHARINDEX(',', a.adrnvn) - 1)), --adr_efternavn
        upper(l.stilling),
        l.stilling,
        substring(case   when a.melnvn is null then  a.fornvn isnull( else a.fornvn, '') + ' ' + isnull(a.melnvn, '') end,1,50),   -- z_fornvn,  fornavn indh. også mlnavne
		a.melnvn,  -- z_mellemnvn
		a.efternvn,
		LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn)+1, LEN(a.adrnvn))),  --z_adr_fornvn
        SUBSTRING(a.adrnvn,1,CHARINDEX(',', a.adrnvn) - 1), -- z_adr_eftermnvn
        a.fornvn,		-- z_fornvn_solo
        a.haenstart_umrk_navne,	-- anvendelse ukendt
        null, --stillingdto udgået
        null, --mynkod_ctnavn  udgået
        null, --myntxt_ctnavn  udgået
        null, --indrap  udgået
        null, -- adrnvndto  udgået
        null,  --soegnvn udgået
        null	--soegnvndto udgået
   FROM folk.lperson2010 l, folk.laktnvnopl2010 a
 WHERE  l.pnr = c.pnr
   AND  l.pnr = a.pnr	
   AND  l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
	  	where  l.lseq BETWEEN 1 AND 6000 );

AnswerRe: Oracle SQL query to MS-SQL Pin
Richard Deeming21-Jan-20 1:43
mveRichard Deeming21-Jan-20 1:43 
GeneralRe: Oracle SQL query to MS-SQL Pin
Member 951930628-Jan-20 22:44
MemberMember 951930628-Jan-20 22:44 
GeneralRe: Oracle SQL query to MS-SQL Pin
Mycroft Holmes29-Jan-20 10:59
professionalMycroft Holmes29-Jan-20 10:59 
Questionsplit time then group by 15 min mongodb Pin
Member 1300557210-Jan-20 21:56
MemberMember 1300557210-Jan-20 21:56 
QuestionSQL Query with a Loop Pin
Bobby Underwood6-Jan-20 4:58
MemberBobby Underwood6-Jan-20 4:58 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 5:04
mva#realJSOP6-Jan-20 5:04 
AnswerRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 5:11
MemberBobby Underwood6-Jan-20 5:11 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 5:16
mva#realJSOP6-Jan-20 5:16 
GeneralRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 5:22
MemberBobby Underwood6-Jan-20 5:22 
AnswerRe: SQL Query with a Loop Pin
Jörgen Andersson7-Jan-20 5:52
professionalJörgen Andersson7-Jan-20 5:52 
Questionincorrect syntax near begin expecting external for Alter Procedure Pin
simpledeveloper27-Dec-19 9:23
Membersimpledeveloper27-Dec-19 9:23 
AnswerRe: incorrect syntax near begin expecting external for Alter Procedure Pin
Jörgen Andersson27-Dec-19 23:14
professionalJörgen Andersson27-Dec-19 23:14 
QuestionAdding multiple columns with condition Pin
simpledeveloper26-Dec-19 14:58
Membersimpledeveloper26-Dec-19 14:58 
AnswerRe: Adding multiple columns with condition Pin
Mycroft Holmes26-Dec-19 19:58
professionalMycroft Holmes26-Dec-19 19:58 
AnswerRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 3:40
professionalJörgen Andersson27-Dec-19 3:40 
GeneralRe: Adding multiple columns with condition Pin
simpledeveloper27-Dec-19 5:29
Membersimpledeveloper27-Dec-19 5:29 
GeneralRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 5:32
professionalJörgen Andersson27-Dec-19 5:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.