BookmarkSubscribeRSS Feed

Re: JSL: Select columns from JMP table and upload to MySQL

rainier1974

New Contributor

Joined:

Feb 12, 2018

Hi, 

Scenario is:

I have a JMP table :JMPCol1,JMPCol2,JMPCol3,JMPCol4,JMPCol5,JMPCol6 and MySQL database: Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8. I want to do insert all rows : Insert INTO DB.Table (JMPCol1,JMPCol3,JMPCol7) values (Field2,Field4,Field1) .

I have not seen any examples online on how to do it. One suggestion was to save to csv then do a bulk insert, but if there is a more direct way of doing it .... 

 

Thanks.  

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

Solution

According to this website this is a valid way to insert multiple values into a table in MySQL:

 

INSERT INTO tableName
    (column1,column2,column3,column4)
VALUES
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4');

So translating that to JSL would be something like this:

dt = New Table( "Test MYSQL Insert",
	Add Rows( 3 ),
	New Column( "Field1", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
	New Column( "Field2", Character, "Nominal", Set Values( {"d", "e", "f"} ) ),
	New Column( "Field3", Character, "Nominal", Set Values( {"g", "h", "i"} ) ),
	New Column( "Field4", Character, "Nominal", Set Values( {"l", "m", "n"} ) )
);
sql = "Insert INTO DB.Table (JMPCol1,JMPCol3,JMPCol7) values ";

for (i = 1, i <= nrows(dt), i++,
	f2 = dt:Field2[i];
	f4 = dt:field4[i];
	f1 = dt:field1[i];
	sql = sql || evalinsert(" ('^f2^', '^f4^', '^f1^'),");
);

// Remove the last comma
len = length(sql);
sql = substr(sql, 1, (len - 1));

 

3 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Solution

According to this website this is a valid way to insert multiple values into a table in MySQL:

 

INSERT INTO tableName
    (column1,column2,column3,column4)
VALUES
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4');

So translating that to JSL would be something like this:

dt = New Table( "Test MYSQL Insert",
	Add Rows( 3 ),
	New Column( "Field1", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
	New Column( "Field2", Character, "Nominal", Set Values( {"d", "e", "f"} ) ),
	New Column( "Field3", Character, "Nominal", Set Values( {"g", "h", "i"} ) ),
	New Column( "Field4", Character, "Nominal", Set Values( {"l", "m", "n"} ) )
);
sql = "Insert INTO DB.Table (JMPCol1,JMPCol3,JMPCol7) values ";

for (i = 1, i <= nrows(dt), i++,
	f2 = dt:Field2[i];
	f4 = dt:field4[i];
	f1 = dt:field1[i];
	sql = sql || evalinsert(" ('^f2^', '^f4^', '^f1^'),");
);

// Remove the last comma
len = length(sql);
sql = substr(sql, 1, (len - 1));

 

Highlighted
pmroz

Super User

Joined:

Jun 23, 2011

Looks like I reversed the JMP dataset and MySQL columns, but you get the general idea.

rainier1974

New Contributor

Joined:

Feb 12, 2018

Thanks pmroz, it works as needed.