SQL Copy Row Data

Targets Windows
1

I’ve been wrestling for a full day now trying to copy all data (except primary key pKey) from CasesListboxColumn = 4 data (see database pic below) to CasesListboxColumn = 2 data (present, but not shown in the image below). I’ve tried a bunch of INSERT TO variations but without any luck. On a few of them, I got a SQL error, while the others ran without error but without any data transfer.

Help me, Obi Wan … you’re my last hope! :wink:

Xojo 2016 r1
Windows 10

SampleDBpic2
SampleDBpic21231×512 127 KB

3

I’m no Obi Wan, but I’m willing to (try to) help! Are you saying you are SELECTing all the data from this table WHERE ‘CasesListboxColumn = 2’, and wanting to insert those found records into another table of similar design?

1 Like
4

Thx for the response, William. I am trying to take all data where CasesListboxRow = 4 and copy it to records where CasesListboxRow = 2.

5

Copy where? another table? same table?

6

Let me be a bit clearer on that:

I’m taking the data from every record that has CasesListboxRow = 4 and copying it to every record that has CasesListboxRow = 2.

Maybe an easier way of saying it is … I’m trying to clone CasesListboxRow = 4 to CasesListboxRow = 2.

7

My bad, Alberto. Copying to the same table. I’ve already inserted the same number of blank records for CasesListboxRows 2 and 3.

8

I’m afraid I’m still unclear. Do you mean you want to duplicate the records that currently have CasesListboxRows = 4 to new records with all the same data except a new id and with CasesListboxRows = 2?

1 Like
9

EXACTLY!!! … Give that man a cigar :wink:

10

Something like this should do it:

INSERT INTO table_name (
  CasesListboxRow,
  CasesListBoxColumn,
  <etc>
) 
SELECT
  2,
  CasesListboxColumn,
  <etc>
FROM
  table_name
WHERE
  CasesListboxRow = 4;

(Not tested.)

11

Wow! That’s incredibly close to one variation I tried (but as you and I both know, being “close” with a SQL statement won’t get you anywhere.)

Let me go give it a try now … I’ll report the result.

Thx, Kem … you’ve always been the “gift that keeps on giving” on this forum!

12

You’re too kind.

Absolutely right, of course, but still…

13

:rofl: :rofl: :rofl: You’re too much!

1 Like
14

OK, here’s what I used for code (I don’t see the code blocks in the response box … what am I missing?) Unfortunately the code as shown below didn’t copy anything …

sql = "INSERT INTO CasesListboxContent (ProposalID, LineupID, CasesListBoxColumn,CellEditControl, CellDataSource) " + _
"SELECT 2, ProposalID, LineupID, CasesListBoxColumn,CellEditControl, CellDataSource " + _
"FROM CasesListboxContent " + _
“WHERE CasesListboxRow = 4”

15

You missed CasesListboxRow as the first column in the INSERT.

1 Like
16

Also, I’m not quite sure about the “SELECT 2,” part ???

17

Where’s the code blocks in the response box??? Am I blind?

18

The columns in the SELECT must match the listed columns in the INSERT.

So

INSERT INTO table (
  col1,
  col2,
  col3
)
SELECT
  2, -- static value into col1
  col2,
  col3
FROM
  table
WHERE
  col1 = some_other_value
19

Use three backticks on their own lines before and after the code.

20

OK … tried this (putting CasesListboxRow in the Destination and Source) with no luck …

  sql = "INSERT INTO CasesListboxContent (ProposalID, LineupID, CasesListboxRow, CasesListBoxColumn,CellEditControl, CellDataSource) " + _
  "SELECT 2, ProposalID, LineupID, CasesListboxRow, CasesListBoxColumn,CellEditControl, CellDataSource " + _
  "FROM CasesListboxContent " + _
  "WHERE CasesListboxRow = 4"
21

Not tested but this is what Kem is saying:

sql = "INSERT INTO CasesListboxContent (CasesListboxRow, ProposalID, LineupID, CasesListBoxColumn,CellEditControl, CellDataSource) " + _
"SELECT 2, ProposalID, LineupID, CasesListBoxColumn,CellEditControl, CellDataSource " + _
"FROM CasesListboxContent " + _
"WHERE CasesListboxRow = 4"

As CasesListboxRow is the first of the Insert and 2 is the first of Select, it will put 2 on the copied records that the select searches for 4. Hope this makes sense.

1 Like