Why some rows can be inserted into and some cannot?

Question

When I use the Cashier table or some other tables that have small amounts of records the process proceeds and table is inserted into the external database. But when I change the cashier into the transaction database (400k+ records), Visual Studio reports an error near "Transaction" Help would be appreciated thanks.

Cashier Database (working)

Dim query As String = "select * into MyDatabase2.dbo.Cashier from bos_primary_db.dbo.Cashier"

Transaction Database (not working)

Dim query As String = "select * into MyDatabase2.dbo.Transaction from bos_primary_db.dbo.Transaction"

This is the error message:

Incorrect syntax near the keyword 'Transaction'

make sure the table dbo.transaction exists in bos_primary_db — Keith Mifsud, May 26 '15 at 08:39
@JamesZ: What's the point of putting a question on hold when it's already have an accepted answer? — Zohar Peled, Jun 09 '15 at 08:24

score 2 · Accepted Answer · edited May 23 '17 at 12:06

this is probably because Transaction is a reserved word in SQL. Depending on your RDBMS (that you didn't specify), there are ways to "escape" it:
for Sql Server, you should wrap reserved words in square brackets:

select * into MyDatabase2.dbo.[Transaction] from bos_primary_db.dbo.[Transaction]

For MySql you should use an apostrophe:

select * into MyDatabase2.dbo.`Transaction` from bos_primary_db.dbo.`Transaction`

For Oracle you should use double quotes:

select * into MyDatabase2.dbo."Transaction" from bos_primary_db.dbo."Transaction"

Note: You should always try to avoid using reserved words. This link describes my favorite way of do it.

Glad I could help. Please consider editing your question to include the relevant RDBMS tag for future users that might have the same problem. — Zohar Peled, May 26 '15 at 09:05

Why some rows can be inserted into and some cannot?

1 Answers1