How to Copy a Row from Table to Table

[notebinder]

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]

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]

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]

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]

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

 

[notebinder]

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]

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]

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]

Well, I’m no expert ...

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

 

[theDBguy]

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]

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]

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

SQL QBE produces this for me?

SELECT Account AS NewAccount, Description
FROM Transactions;

Just curious now.