SQL Server: Publishing a table to a specific Schema

Hello All,

I am looking for help regarding how to push a table from KNIME to SQL Server in a specific Schema. I have a connection to my server and database. Now I have a table created through KNIME that I want to put on:

[<my_db>].[<a_schema>].[<new_table>]

With the Database Writer node I can currently create:

[<my_db>].[<new_table>]

but cannot figure out how to place that into a schema. 

Could someone give me a hand ? 

Cheers,

Matt

Hi Matt,

did you already give the database table creator a try? There you should be able to specify a table schema. Hope that helps.

Cheers,
Marten

Hello Marten,

Thanks for your answer! 

I have tried the table creator indeed, but I don't understand how to add data generated through KNIME to the table.

Basically, when I go on SQL server to check if the table has been created, I see the table columns and specs put not the data.

If I use the database writer, I don't know how to specify the table i created (since it is in a specific schema)...

Do you have an idea of how to get that done?

Matthieu

The table name is include schema like dbo.Table1. From other side if you created table without schema specified then you do not need to bother, you have access only to one schema based on your authorities.

Hello! Thanks for your answer. 

I have acccess to the schemas I want to write to, that is not an issue (it has been tested with other software).

Concerning the name of the table, If I write dbo.Table1 as the name of the table, the table created in the database will be name "dbo.Table1", literally, and be placed right on the database, not within the dbo Schema.

I've bypassed this problem with a Python Snippet that connects to the DB and writes the table to the right place, but this has caused performance issues...

I'm open to any suggestion!

Thank you :)

Matt

Hi Matthieu,

Happy new year and sorry for the late response. The Database Table Creator node is really only designed for the creation of a DB table. And if you want to create a table with a specific schema, that is the way to go. Afterwards you can use the Database Writer node to write your data from KNIME to the DB. Does that help?

Best,
Marten