How to Copy a Row from Table to Table (1 Viewer)

notebinder · Jan 19, 2019

I have 2 simple tables with 3 columns and an autonumber primary key/ID column. One table is a TEMPLATE table from which I copy data into another NOTE table to create a new record. During this copy, I rename the template name field to create the new entry name. In MySql, this was very simple SQL:

INSERT INTO notes (name, note, data_size)
SELECT 'newname' AS name, note, data_size FROM templates WHERE id = 99

The new row in the NOTES table is now named newname. This SQL does not work in Access and I can't see a way around it. Anyone have any ideas?

theDBguy · Jan 19, 2019

Hi. I don’t see why it won’t work other than maybe you need to enclose name in square brackets because “name” is a reserved word in Access. Are you getting an error?

isladogs · Jan 19, 2019

Cross posted at http://www.accessforums.net/showthread.php?t=75306

Please read this article about the etiquette of cross posting https://www.excelguru.ca/forums/showthread.php?518-Article-A-message-to-forum-cross-posters

notebinder · Jan 19, 2019

theDBguy said:
Hi. I don’t see why it won’t work other than maybe you need to enclose name in square brackets because “name” is a reserved word in Access. Are you getting an error?

It fails even if I use [name]. The error is "Syntax error in INSERT INTO statement" (very informative isn't it)

Gasman · Jan 19, 2019

Why do you have single quotes with newname and not the others?

notebinder · Jan 19, 2019

Gasman said:
Why do you have single quotes with newname and not the others?

newname is the new value of the NAME field that I use for the new row.

theDBguy · Jan 19, 2019

Well, I’m no expert but the syntax looks correct to me. Perhaps we’ll need to see what you’re working with. Can you post a demo db or some screenshots? What is your table structure like?

notebinder · Jan 19, 2019

So I played with this based on theDBguy's feedback on reserved words. It now works if I use[] for all fields (not sure of the logic there, but .....):

INSERT INTO notes ([name], [note], [data_size]) SELECT 'newname' AS [name], [note], [data_size] FROM templates WHERE id = 99

Thanks for all the responses.

isladogs · Jan 19, 2019

theDBguy said:
Well, I’m no expert ...

LOL...you're far too modest and an MVP to boot.

theDBguy · Jan 19, 2019

notebinder said:
So I played with this based on theDBguy's feedback on reserved words. It now works if I use[] for all fields (not sure of the logic there, but .....):

INSERT INTO notes ([name], [note], [data_size]) SELECT 'newname' AS [name], [note], [data_size] FROM templates WHERE id = 99

Thanks for all the responses.

Hi. Glad to hear you got it sorted out. Good luck with your project.

theDBguy · Jan 19, 2019

isladogs said:
LOL...you're far too modest and an MVP to boot.

Hi Colin. Thanks. I have a tagline I use at UA that I borrowed from Kansas. Maybe I should use it here too.

Gasman · Jan 19, 2019

notebinder said:
newname is the new value of the NAME field that I use for the new row.

SQL QBE produces this for me?

Code:

SELECT Account AS NewAccount, Description
FROM Transactions;

Just curious now.

How to Copy a Row from Table to Table (1 Viewer)

notebinder

New member

theDBguy

I’m here to help

isladogs

MVP / VIP

notebinder

New member

Gasman

Enthusiastic Amateur

notebinder

New member

theDBguy

I’m here to help

notebinder

New member

isladogs

MVP / VIP

theDBguy

I’m here to help

theDBguy

I’m here to help

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread