3

Is there any way i could make a sql script file compatible with both MYSQL and MSSQL? MYSQL and MSSQL should be able to use that file to execute the SQL scripts contained in the file. The following SQL statement is just an example.

   CREATE TABLE testTable(
   Name VARCHAR(20),
   Address VARCHAR(50),
   Gender VARCHAR(10)
   );

   INSERT INTO testTable
   VALUES
   ('Derp', 'ForeverAlone Street', 'Male'),
   ('Derpina', 'Whiterun Breezehome', 'Female');

   SELECT * FROM testTable;

PS, I heard u could use CSV files. I dont know if its true. I dont see any proper examples online.

EDIT: NO you cant use csv files to execute statements. They are used to dump data. Some sql statements are written differently in MS SQL and MYSQL. Common statements such as Create, select statements are the same in both MS SQL and MY SQL. one of the difference could be LOAD DATA INFILE and BULK INSERT FROM

user921020
  • 355
  • 1
  • 3
  • 6
  • 1
    Umm... don't use any features that are exclusive to one or the other? Why can't you provide a separate script for each? This seems like a rather arbitrary requirement. – mellamokb Jul 24 '12 at 14:09
  • Im making a java program which helps users export data in a table. Its like an additional feature to my system so that the user dont have to use seperate script files. Ive heard u could do it using CSV files. However, csv files is only used for bulk insert. Thanks anyways – user921020 Jul 24 '12 at 14:16

1 Answers1

1

using ansi sql will avoid most problems but I'm not sure if it's possible to have something that's 100% cross compatible in all cases

See here for more info: ANSI SQL Manual

Community
  • 1
  • 1
Andrew Walters
  • 4,763
  • 6
  • 35
  • 49
  • Lol thanks. Hmm, ive read the link u sent me. Its similiar to what im looking for but its isnt what im looking for. Oh well, i guess u are rite. U cant have cross compatible sql scripts. – user921020 Jul 24 '12 at 14:22
  • It really depends what you're needing to do though. If it's just simple select, join, group ect. everything should be ok. It's when you want to do some of the Ms SQL or Oracle only stuff like APPLY or cursors that queries loose compatibility – Andrew Walters Jul 24 '12 at 14:26
  • I see i see. So Lets take the above shown sql script as an example. If i save the script as test.sql, then, MSSQL and MYSQL should be able to to make use of test.sql? – user921020 Jul 24 '12 at 14:29
  • I think so, the only thing I'd change is to split out each insert into it's own statement, So INSERT INTO testTable VALUES ('Derp', 'ForeverAlone Street', 'Male'); INSERT INTO testTable VALUES ('Derpina', 'Whiterun Breezehome', 'Female'); I haven't tested to verify they'll both run but with just an insert you can get something to run in all the major sql databases – Andrew Walters Jul 24 '12 at 14:33
  • Hmm oh yes i get what u mean. Im downloading mssql now to test out. Thanks man! – user921020 Jul 24 '12 at 14:54