Copying Individual Tables with dbatools

@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

  1. 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.
  2. Take the CREATE TABLE script that we just exported from the source database and run it against the destination.
  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *