Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Insert INTO

  1. How-to
0
Votes
Undo
  1. Andrew Davis
    Andrew Davis
  2. PowerBuilder
  3. Thursday, 9 April 2020 16:01 PM UTC

Hi

I do hope everyone is well.

I am trying to do an insert into sql statement within powerbuilder script. PB2019

 

I want to copy all the record from sales to sales_history - sales_history has all the fields of sales but in addition a archive_year and a archive_month field 

this is the sort of statement i think i need 

INSERT INTO sales_history (column1, column2, column3, :li_archive_year , :li_archive_month )
SELECT column1, column2, column3
FROM sales

Any help much appreciated

regards

 

Andrew

UPDATE - 

 So - pb2019 

dbase sqlanywhere16 - client machine

 

  1. SQL
Ribbonbar control : Find a menu by tag
Converting PB apps to Web apps
Accepted Answer
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 9 April 2020 18:42 PM UTC
  2. PowerBuilder
  3. # Permalink

Not sure about SQLAnywhere syntax, but the following would work in MS SQL Server from PowerScript:

Integer li_year, li_month

li_year = Year(Today())
li_month = Month(Today())

INSERT INTO sales_history ( col1, col2, ..., coln, archive_year, archive_month)
SELECT col1, col2, ..., coln, :li_year, :li_month
  FROM sales
 USING SQLCA;

HTH, John

Comment
Andrew Davis
  1. Andrew Davis
  2. Friday, 10 April 2020 10:33 AM UTC
John

Awesome thank you for your help, worked perfectly.



as always this community is excellent.



regards and happy Easter



andrew
  1. Helpful
John Fauss
  1. John Fauss
  2. Friday, 10 April 2020 13:47 PM UTC
I'm glad to help. Happy Easter to you & yours, also. Be safe!
  1. Helpful
There are no comments made yet.
Responses (3)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 10 April 2020 05:48 AM UTC
  2. PowerBuilder
  3. # 1

Hi Andrew,

I think John already gave you the correct answer, just to emphasize that your suggested code:

INSERT INTO sales_history (column1, column2, column3, :li_archive_year , :li_archive_month )
SELECT column1, column2, column3
FROM sales

is wrong. The li_archive_year and li_archive_month should be in the SELECT part and not where you've put it in the list of columns.

regards

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 9 April 2020 18:07 PM UTC
  2. PowerBuilder
  3. # 2

Greetings, Andrew -

You did not give us much information...

What DBMS vendor and version? Local DB (on the same PC as the app) or remote? How many records will be archived? What PB version are you using? Is security/permissions an issue? If your DBMS supports stored procedures, I suggest you consider creating one to do this.

Comment
Andrew Davis
  1. Andrew Davis
  2. Thursday, 9 April 2020 18:19 PM UTC
John, thanks

Just added the vendor infor to the quesiton - pb2019 - sqlanywhere 16 - client machine - i realise i could add a stored procedure - but i was hoping to just - run the sql statement

its about 4000 records - security not an issue
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.