Pull code from on prem database to create AZURE SQL table including indexes, constraints, keys, etc. using pyspark

Question

I need to fetch the 'CREATE TABLE' code with all primary keys,indexes, collation etc. from on prem SQL Server db to Databricks and then from Databricks to Azure SQL db.

I have already connection to both SQL Server databases from Databricks. I'm using JDBC driver.

If you only need it once, you can script tables by SSMS tool, right click on table in the Object Explorer and choose Script table — siggemannen
– siggemannen, Commented Jun 19, 2023 at 21:07

David Browne - Microsoft · Accepted Answer · 2023-06-19 19:00:56Z

1

There's no built-in TSQL api to generate full-fidelity DDL for existing objects. The Sql Server Management Objects (SMO) .NET library is the official component that does DDL generation.

So not the most natural thing to use from Databricks, but technically possible.

Users have created more or less elaborate TSQL solutions for this for decades. See: How to generate create script of table using SQL query in SQL Server for some links.

If you don't need full fidelity, you can just use Spark JDBC to open a DataFrame from one SQL Server and write it to the other.

answered Jun 19, 2023 at 19:00

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pull code from on prem database to create AZURE SQL table including indexes, constraints, keys, etc. using pyspark

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related