# Make File to Export, Truncate, and Insert (Merge) SQL data from Remote Server to Local

**URL:** <https://forum.codeselfstudy.com/t/make-file-to-export-truncate-and-insert-merge-sql-data-from-remote-server-to-local/2443>\
**Category:** Tutorials\
**Created:** [October 13, 2020, 5:40am UTC](https://forum.codeselfstudy.com/t/make-file-to-export-truncate-and-insert-merge-sql-data-from-remote-server-to-local/2443 "2020-10-13T05:40:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![PaulH](https://forum.codeselfstudy.com/letter_avatar_proxy/v4/letter/p/e495f1/32.png) [@PaulH](https://forum.codeselfstudy.com/u/PaulH)\
**Post date:** [October 13, 2020, 5:40am UTC](https://forum.codeselfstudy.com/t/make-file-to-export-truncate-and-insert-merge-sql-data-from-remote-server-to-local/2443/1 "2020-10-13T05:40:35Z")

</div>

I had an issue recently where I need to download activity and feedback data from the remote server. The tables were getting quite large ~500MB so I needed way to truncate them and insert them to a local version. From the local host I can run all kind of analysis.

The way I did this is using a make file with the following commands.

1. From local command line use ssh to log into the remote server and MySQL dump to export the table. If you do not have SSH please find a tutorial and set that up.

2. Truncate the table using ssh and then mySQL

```auto
ssh user@1.1.1.1 'mysqldump -u USER -pXXXXX db_name tableName > tableName.sql'
ssh user@1.1.1.1 'mysql -u USER -pXXXXX db_name -e "TRUNCATE TABLE tableName"'

```

1. Next we send the table from remote to local using scp

```auto
scp user@1.1.1.1:/remoteOriginPath/tableName.sql ./destinationPath/

```

1. Import the downloaded table to a database just for this kind of imported data

```auto
mysql -u localUser db_import < ./destinationPath/tableName.sql
	

```

1. Next you can run any SQL operations to mutate the data, or execute a file from command line

```auto
mysql -u user db_import -e "UPDATE tableName SET value=value+example"

```

1. The last step in the make recipe is to import the data to the existing table. This way you just add on the most recent updates

```auto
mysql -u user db_import -e "INSERT INTO localDB.tableName SELECT * FROM tableName"

```

And that should be it. Please ask questions below. What is your method? What is the better way to do this? Look forward to hearing your answers. 🙂

---

<div class="post-metadata">

**Author:** ![Josh](https://forum.codeselfstudy.com/user_avatar/forum.codeselfstudy.com/josh/32/4_2.png) [@Josh](https://forum.codeselfstudy.com/u/Josh)\
**Post date:** [October 14, 2020, 7:23am UTC](https://forum.codeselfstudy.com/t/make-file-to-export-truncate-and-insert-merge-sql-data-from-remote-server-to-local/2443/2 "2020-10-14T07:23:03Z")

</div>

I’d probably do it with shell script, something like that. 🙂

> [@PaulH](#):
>
> What is the better way to do this?

I don’t know if it’s better, but if you’re using node now, you can also run scripts from your `package.json` file.

If what you’re doing now works, no need to change, but if you want to do it from `package.json`, you could put all your shell commands in a file like `scripts/backup.sh` (or whatever you want to name it). As you make new scripts, add them to the `scripts` directory.

Then in each file:

```bash
#!/bin/bash

echo 'hello world'
# put your commands here

```

The first line tells the computer what interpreter to use (`bash`).

Make the file executable so it can be run as a program:

```plaintext
$ chmod +x scripts/backup.sh

```

Then in the `scripts` section of `package.json`:

```javascript
"scripts": {
    // the dot is required to run the script
    "backup": "./scripts/backup.sh",
    // other scripts can go here
}

```

To run the script, type `npm run backup` (or whatever you named it in `package.json`).

If you don’t add it to `package.json` you can manually run it by typing in the path to the file with a leading dot, and it will do the same thing:

```plaintext
$ ./scripts/backup.sh

```

I don’t know if there is any advantage to that way — just another option for running scripts if you’re using node. 🙂

---

<div class="post-metadata">

**Author:** ![PaulH](https://forum.codeselfstudy.com/letter_avatar_proxy/v4/letter/p/e495f1/32.png) [@PaulH](https://forum.codeselfstudy.com/u/PaulH)\
**Post date:** [October 16, 2020, 9:02pm UTC](https://forum.codeselfstudy.com/t/make-file-to-export-truncate-and-insert-merge-sql-data-from-remote-server-to-local/2443/3 "2020-10-16T21:02:25Z")

</div>

Thanks Josh!