Database Insert activity should bracket column names with spaces

Hi.
Is there a way to avoid removing spaces or special characters from Database column names and make the query work. Possibly using some escape characters or variables in the Execute query activity which will avoid these errors?

Let me know.

Thanks.

1 Like

For column names, you can use like this [my column name]

Thanks Bruno.
But can this syntax to specify column names as [my column name] be used in Insert Database activity as well, where an entire datatable is being inserted into the database table?
If yes, can you please provide me with a sample syntax, please.

Thanks, and appreciate your response.

Regards.

Hi, do you mean the below activity?


Do you have error using this? If the column names are exactly the same as in your database, then you should be ok…

Yes. Thats the activity I am using, but this activity is ended with execution errors even though the column names between the datatable and DB table are the same.

Two errors encountered:
Error 1 (Space issue) : Incorrect syntax near ‘PAYMENT’. Unknown object type ‘DTE’ used in a CREATE, DROP, or ALTER statement.Unknown object type ‘USERID’ used in a CREATE, DROP, or ALTER statement , Incorrect syntax near ‘BENCHMARK’.
Error 2 (Keyword issue) : Incorrect syntax near the keyword ‘ALL’ , Incorrect syntax near the keyword ‘TABLE’.

Regards.

Is your table having column names with spaces? Are you using a non-beta version of the packages in your project?

Yes. Database tables have column names with spaces.
And below is the screenshot of the Database package version in use.

image

Regards.

ok, i will run some tests here, to see if adding the brackets can help.

Im afraid it wont work and we might have a bug here (@loginerror )… For now, you have a couple of options, assuming you cant change the column names…

  1. Insert one at a time using execute non-query and using the brackets there
  2. Create a stored procedure to receive multiple rows and do what needed in there

Thanks, Bruno. Appreciate your help.

There has just been some discussion around it here:

I suppose it could indeed be improved.

I will mark this topic as #pr-welcome as the activity is one of our open-sourced ones and we are happy for one of our community members to have a look and improve it :slight_smile:

3 Likes