database insert

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
macrolife
Newbie
Posts: 1
Joined: Thu Nov 12, 2020 8:24 pm

database insert

Post by macrolife » Tue Apr 06, 2021 9:00 pm

Hello Everyone its been a while since I been on the forum.

I am trying to insert data into a database.
Their is examples of how to Delete and modify data.
But I can find no clear example or code snippet of how to insert data into a table.
As a practice I am using the standard MS AdventureWorks2012 database.

Any input would be great.

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: database insert

Post by mightycpa » Wed Apr 07, 2021 1:32 am

From the docs, slightly modified:

Code: Select all

Let>str=Driver={MySQL ODBC 3.51 Driver};Server=someserver.com;Port=3306;Database=example;User=admin;Password=xxxx;Option=3;
DBConnect>str,dbH
 
Let>SQL=insert into mytable (serial, name, rating) values ('12345', 'mightycpa', 10)
DBExec>dbH,SQL,result
 
DBClose>dbH
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

User avatar
stitch22
Junior Coder
Posts: 31
Joined: Mon Oct 20, 2008 3:18 pm
Location: California
Contact:

Re: database insert

Post by stitch22 » Sun Apr 11, 2021 5:20 am

First look, missing end of line ";" on your SQL insert satement. Also the "ANSI" or "UNICODE" in the driver statement.
Driver={MySQL ODBC 5.2 ANSI Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: database insert

Post by mightycpa » Mon Apr 12, 2021 4:33 am

Hi,

My experience is that the semi-colon isn't needed. In fact, I don't know that I've ever tried adding one, and I've got a ton of sql statements, different databases. All of it worked.

This is a pretty good resource: https://www.connectionstrings.com/

These connection statements have worked for me in the past:

MySQL
DBConnect>Provider=MSDASQL.1;Persist Security Info=False;Data Source=my_database,dbH

SQL Server
Let>my_conn=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DBNAME;Data Source=server.houston.xxx.com;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ADMINIB-Q90DN0M;Use Encryption for Data=False;Tag with column collation when possible=False

TXT file
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties='text;HDR=YES;FMT=Delimited'
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

User avatar
stitch22
Junior Coder
Posts: 31
Joined: Mon Oct 20, 2008 3:18 pm
Location: California
Contact:

Re: database insert

Post by stitch22 » Mon Apr 12, 2021 12:02 pm

Hello,
That is a great site. I was going to refer there myself. I see alot of sesmicolon's in the those groups of statements. I'm guessing they all worked except the last ones with out it. I just expierenced it with my last SQL insert. Took me a while to figure it out.

SQL=Insert into table_name select alpha.`GTIN Number`AS gtin, CC+FO+KC+MA+PH+TD+CN+GD AS qty,NOW() FROM alpha;

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts