convert this code into my sql compatible code

Question

ramnik.bhat1988 0 Newbie Poster

9 Years Ago

CREATE TABLE cash_dtL (
  Cash_id varchar(12) NOT NULL,
  Dateof date NOT NULL,
  Cash_in_hand number(8) NOT NULL,
  Cash_in_bank number(8) NOT NULL,
  Sy_dr number(8) NOT NULL,
  Property number(8) NOT NULL,
  FD number(8) NOT NULL,
  PRIMARY KEY (Cash_id)
);
INSERT INTO cash_dtL values('001', '01-JAN-15', 1000, 12000, 1200, 0, 1200);
INSERT INTO cash_dtL values('002', '02-JAN-15', 2000, 121000, 0, 12000, 0);
INSERT INTO cash_dtL values('003', '03-JAN-15', 2000, 121, 10, 0, 999);
INSERT INTO cash_dtL values('004', '04-JAN-15', 1200, 125000, 2000, 0, 200000);
INSERT INTO cash_dtL values('005', '05-JAN-15', 1200, 123000, 2000, 125000, 200000);

CREATE TABLE liabilities (
  Cash_id varchar(12) NOT NULL,
  Dateof date NOT NULL,
  gen_fund number(8) NOT NULL,
  internet_fund number(8) NOT NULL,
  photostat number(8) NOT NULL,
  print number(8) NOT NULL,
  lamination number(8) NOT NULL,
  binding number(8) NOT NULL,
  petty_cash number(8) NOT NULL,
  capital number(8) NOT NULL,
  sycr number(8) NOT NULL
);
INSERT INTO liabilities VALUES(1, '01 JAN 15', 458, 13, 50, 72, 941, 5, 2, 83, 0);
INSERT INTO liabilities VALUES(2, '02 JAN 15', 222222, 12222, 11233, 3555, 2222, 22222, 5000, 200000, 2000);
INSERT INTO liabilities VALUES (3, '03 JAN 15', 290000, 12222, 12221, 13331, 11111, 1111, 7000, 123000, 900);
INSERT INTO liabilities VALUES (3, '05 JAN 15', 22222, 12222, 33333, 11111, 11111, 22222, 12221, 123333, 1111);

CREATE TABLE  master_cash (
  sno number(12) NOT NULL,
  Cash_id number(12) NOT NULL,
  Dateof varchar2(18) NOT NULL
);

INSERT INTO master_cash  VALUES(1, 1, '01 JAN 15');
INSERT INTO master_cash  VALUES(2, 2, '02 JAN 15');
INSERT INTO master_cash  VALUES(3, 3, '03 JAN 15');

CREATE TABLE  master_rank (
  rank_id number(8) NOT NULL,
  rank varchar2(9) NOT NULL
); 


INSERT INTO master_rank  VALUES(1, 'sigmn');
INSERT INTO master_rank  VALUES(2, 'lnk');
INSERT INTO master_rank  VALUES(3, 'nk');
INSERT INTO master_rank  VALUES(4, 'hav');
INSERT INTO master_rank  VALUES(5, 'nb sub');
INSERT INTO master_rank  VALUES(6, 'sub');
INSERT INTO master_rank  VALUES(7, 'sub maj');
INSERT INTO master_rank  VALUES(8, 'lt');
INSERT INTO master_rank  VALUES(9, 'capt');
INSERT INTO master_rank  VALUES(10, 'maj');
INSERT INTO master_rank  VALUES(11, 'lt col');
INSERT INTO master_rank  VALUES(12, 'col');
INSERT INTO master_rank  VALUES(13, 'brigadier');
INSERT INTO master_rank  VALUES(14, 'maj gen');

CREATE TABLE  members (
  username varchar2(8) NOT NULL,
  password varchar2(8) NOT NULL
);

INSERT INTO members  VALUES('regtfund', 'abc123');
CREATE TABLE receipt_table (
  Dateof date NOT NULL,
  RV_No varchar2(9) NOT NULL,
  Cash_recd number(9) NOT NULL,
  On_Account varchar2(16) NOT NULL,
  Recd_from varchar2(16) NOT NULL
) ;
INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('01-jan-15', 'rv/001', 3000, 'cyber usage', 'CDA');
INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('02-jan-15', 'rv/002', 3000, 'cyber usage', 'CDA');
INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('03-jan-15', 'rv/003', 3000, 'cyber usage', 'CDA');


CREATE TABLE pv_table (
  PV_id NUMBER(6) NOT NULL,
  PV_No VARCHAR2(9) NOT NULL
) ;
INSERT INTO pv_table VALUES(01, 'pv/001');
INSERT INTO pv_table VALUES(02, 'pv/002');
INSERT INTO pv_table VALUES(03, 'pv/003');

CREATE TABLE  rv_table (
  Rv_id NUMBER(6) NOT NULL,
  RV_No VARCHAR2(9)NOT NULL
) ;
INSERT INTO rv_table  VALUES(01, 'rv/001');
INSERT INTO rv_table  VALUES(02, 'rv/002');
INSERT INTO rv_table  VALUES(03, 'rv/003');
CREATE TABLE pers_dtl (
  Army_No varchar2(9) NOT NULL,
  Rank   varchar2(8) NOT NULL,
  Name  varchar2(10) NOT NULL
) ;
CREATE TABLE pers_dtl (
  Army_No varchar2(9) NOT NULL,
  Rank   varchar2(8) NOT NULL,
  Name  varchar2(10) NOT NULL
) ;
INSERT INTO pers_dtl  VALUES('JC1234A', 'SubMaj', 'SCJOSHI');
INSERT INTO pers_dtl  VALUES('SS-45974L', 'CAPT', 'RAMNIK');
INSERT INTO pers_dtl  VALUES('IC-12345', 'Maj', 'MGSingh');

mysql

4 Contributors
3 Replies
330 Views
2 Days Discussion Span
Latest Post 9 Years Ago Latest Post by Reverend Jim

pixelsoul 272 Red Pill

9 Years Ago

No? http://www.sqlines.com/sql-server-to-mysql

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rch1231 169 Posting Shark · Answer 1 · 2015-01-26T06:27:30+00:00

I see two problems with the sql that would need to be changed for mysql:

1 replace number wth int and replace varchar2 with varchar

2 You have the create table pers_dtl code listed twice:

I ran the following on myserver and it worked fine.

    CREATE TABLE cash_dtL (
    Cash_id varchar(12) NOT NULL,
    Dateof date NOT NULL,
    Cash_in_hand int(8) NOT NULL,
    Cash_in_bank int(8) NOT NULL,
    Sy_dr int(8) NOT NULL,
    Property int(8) NOT NULL,
    FD int(8) NOT NULL,
    PRIMARY KEY (Cash_id)
    );
    INSERT INTO cash_dtL values('001', '01-JAN-15', 1000, 12000, 1200, 0, 1200);
    INSERT INTO cash_dtL values('002', '02-JAN-15', 2000, 121000, 0, 12000, 0);
    INSERT INTO cash_dtL values('003', '03-JAN-15', 2000, 121, 10, 0, 999);
    INSERT INTO cash_dtL values('004', '04-JAN-15', 1200, 125000, 2000, 0, 200000);
    INSERT INTO cash_dtL values('005', '05-JAN-15', 1200, 123000, 2000, 125000, 200000);
    CREATE TABLE liabilities (
    Cash_id varchar(12) NOT NULL,
    Dateof date NOT NULL,
    gen_fund int(8) NOT NULL,
    internet_fund int(8) NOT NULL,
    photostat int(8) NOT NULL,
    print int(8) NOT NULL,
    lamination int(8) NOT NULL,
    binding int(8) NOT NULL,
    petty_cash int(8) NOT NULL,
    capital int(8) NOT NULL,
    sycr int(8) NOT NULL
    );
    INSERT INTO liabilities VALUES(1, '01 JAN 15', 458, 13, 50, 72, 941, 5, 2, 83, 0);
    INSERT INTO liabilities VALUES(2, '02 JAN 15', 222222, 12222, 11233, 3555, 2222, 22222, 5000, 200000, 2000);
    INSERT INTO liabilities VALUES (3, '03 JAN 15', 290000, 12222, 12221, 13331, 11111, 1111, 7000, 123000, 900);
    INSERT INTO liabilities VALUES (3, '05 JAN 15', 22222, 12222, 33333, 11111, 11111, 22222, 12221, 123333, 1111);
    CREATE TABLE master_cash (
    sno int(12) NOT NULL,
    Cash_id int(12) NOT NULL,
    Dateof varchar(18) NOT NULL
    );
    INSERT INTO master_cash VALUES(1, 1, '01 JAN 15');
    INSERT INTO master_cash VALUES(2, 2, '02 JAN 15');
    INSERT INTO master_cash VALUES(3, 3, '03 JAN 15');
    CREATE TABLE master_rank (
    rank_id int(8) NOT NULL,
    rank varchar(9) NOT NULL
    );
    INSERT INTO master_rank VALUES(1, 'sigmn');
    INSERT INTO master_rank VALUES(2, 'lnk');
    INSERT INTO master_rank VALUES(3, 'nk');
    INSERT INTO master_rank VALUES(4, 'hav');
    INSERT INTO master_rank VALUES(5, 'nb sub');
    INSERT INTO master_rank VALUES(6, 'sub');
    INSERT INTO master_rank VALUES(7, 'sub maj');
    INSERT INTO master_rank VALUES(8, 'lt');
    INSERT INTO master_rank VALUES(9, 'capt');
    INSERT INTO master_rank VALUES(10, 'maj');
    INSERT INTO master_rank VALUES(11, 'lt col');
    INSERT INTO master_rank VALUES(12, 'col');
    INSERT INTO master_rank VALUES(13, 'brigadier');
    INSERT INTO master_rank VALUES(14, 'maj gen');
    CREATE TABLE members (
    username varchar(8) NOT NULL,
    password varchar(8) NOT NULL
    );
    INSERT INTO members VALUES('regtfund', 'abc123');
    CREATE TABLE receipt_table (
    Dateof date NOT NULL,
    RV_No varchar(9) NOT NULL,
    Cash_recd int(9) NOT NULL,
    On_Account varchar(16) NOT NULL,
    Recd_from varchar(16) NOT NULL
    ) ;
    INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('01-jan-15', 'rv/001', 3000, 'cyber usage', 'CDA');
    INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('02-jan-15', 'rv/002', 3000, 'cyber usage', 'CDA');
    INSERT INTO receipt_table (Dateof, RV_No, Cash_recd, On_Account, Recd_from) VALUES('03-jan-15', 'rv/003', 3000, 'cyber usage', 'CDA');
    CREATE TABLE pv_table (
    PV_id int(6) NOT NULL,
    PV_No VARCHAR(9) NOT NULL
    ) ;
    INSERT INTO pv_table VALUES(01, 'pv/001');
    INSERT INTO pv_table VALUES(02, 'pv/002');
    INSERT INTO pv_table VALUES(03, 'pv/003');
    CREATE TABLE rv_table (
    Rv_id int(6) NOT NULL,
    RV_No VARCHAR(9)NOT NULL
    ) ;
    INSERT INTO rv_table VALUES(01, 'rv/001');
    INSERT INTO rv_table VALUES(02, 'rv/002');
    INSERT INTO rv_table VALUES(03, 'rv/003');
    CREATE TABLE pers_dtl (
    Army_No varchar(9) NOT NULL,
    Rank varchar(8) NOT NULL,
    Name varchar(10) NOT NULL
    ) ;

    INSERT INTO pers_dtl VALUES('JC1234A', 'SubMaj', 'SCJOSHI');
    INSERT INTO pers_dtl VALUES('SS-45974L', 'CAPT', 'RAMNIK');
    INSERT INTO pers_dtl VALUES('IC-12345', 'Maj', 'MGSingh');

score 0 · Answer 2 · 2015-01-26T13:44:37+00:00

I also suggest that it is bad form to omit field names when doing inserts. You should force correspondence between field names and values by using the form

INSERT INTO tablename (fldname1, fldname2, ...)
                VALUES(value1, value2, ...)

I don't have SQL installed at the moment (new laptop) but as I recall you can do the following for inserting multiple records

INSERT INTO tablename (fldname1, fldname2, ...)
                VALUES(value1, value2, ...),
                VALUES(value1, value2, ...),
                VALUES(value1, value2, ...),
                VALUES(value1, value2, ...)