Why can't I use the Import Data Wizard (on SSMS) to alter the default schema for new destination tables?

Question

Hello,

I hope this question is clear. I am still in the process of adding an image of the error message.

I am using the wizard to import a flat file source into my database. I have SQL Server Express 2019.
My goal is to create a table with the [raw] schema instead of the [dbo] schema the wizard default creates for me.

When I come to the stage in the wizard where I can edit mappings, I am able to view and edit the wizard-generated T-SQL query. If I change the bracketed [dbo] to [raw] and then execute the query, I have multiple errors in creating the table, specifically in the pre-execute stage.

Why isn't modifying the T-SQL sufficient to change the schema?

Answer 1

I think the problem was that after you edited the SQL query, i.e., from [dbo].[Temp] to [raw].[Temp], you have to edit the Destination table list to make sure the name is the same as the one you updated in the SQL query since it does not change automatically.

Answer 2

Hi hapless,

Did you try to alter the default schema directly in this box as below screenshot? I test this in my environment, it works.

Best regards,
Cathy