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.
database insert
Moderators: Dorian (MJT support), JRL
Re: database insert
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
Re: database insert
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;
Driver={MySQL ODBC 5.2 ANSI Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;
Re: database insert
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'
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
Re: database insert
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;
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;