@SQLMonkeyNYC asked on Twitter this morning:
Pat Phelan replied, suggesting that dbatools can do it, but after thinking on it for a bit and poking at a few functions, I realized that it’s not possible with a single function (yet). No worries. We can do it in three lines for now.
Explanation
- Grab the table from the source database and export the create script. I had to use
-PassThru
because otherwise, Export-DbaScript
will create a file. Not the worst thing, but writing the file and the reading it immediately afterwards is a bit messy. Although now that I write that out, I suppose I could capture the filename that’s output and pass that to -QueryFile
in the next step. Gotta love PowerShell - there’s always a few ways to do things.
- Take the
CREATE TABLE
script that we just exported from the source database and run it against the destination.
- Run
Copy-DbaDbTableData
to bring the data over from the source to the destination.
This is a really quick & dirty example where I’m copying between two databases on the same instance, but should give you the idea. You can copy across instances and copy multiple tables as well.