# Import SQL data on an existing server

{% hint style="info" %}
**When to use this**: you setup UXWizz on a new server, but forgot to import your old data. The new server already has new data, so you can't directly import old data, because the incremental IDs will clash.
{% endhint %}

This page explains how you can import SQL data that contains incremental IDs in an existing, already-populated database.

Some tips in the [migration tutorial](https://docs.uxwizz.com/guides/migrating-to-a-new-server) could help for this too.

**Steps to import old SQL data in a UXWizz server that already has data:**

1. **Old server:** Purge old data (to make the DB smaller)
   * Run the queries from [limit record/heatmaps 60 days](https://docs.uxwizz.com/installation/optimization-tips/auto-delete-old-data-cron-jobs#built-in-example-scripts) cron scripts.
2. **Old server:** Export old.sql DB from phpMyAdmin
   * Only export data, no table structure (otherwise manually replace CREATE TABLE with CREATE TABLE IF NOT EXISTS into the exported SQL file)
   * Don't export indexes (or manually remove Index creations section in the SQL file)
3. **New server:** Add a large value to all IDs in the new database (see SQL script below)
4. **New server:** Backup of current SQL file on the new server
5. **New server:** Import old.sql data.

### SQL script to increase all IDs (in order to make room for old data)

{% hint style="warning" %}
This script was tested on UXWizz version 8.5.0. If a new version introduced any database structure changes, you might have to update the script to include all missing tables/IDs.
{% endhint %}

```sql
-- Change 10million to a larger value (up to 99 million) if you had more data stored

-- Disable FK checks
SET FOREIGN_KEY_CHECKS=0;
SET @offset = 10000000;

START TRANSACTION;

-- Primary keys
UPDATE ust_users SET id = id + @offset;
UPDATE ust_clients SET id = id + @offset;
UPDATE ust_clientpage SET id = id + @offset;
UPDATE ust_client_tag SET id = id + @offset;
UPDATE ust_client_event SET id = id + @offset;
UPDATE ust_clicks SET id = id + @offset;
UPDATE ust_movements SET id = id + @offset;
UPDATE ust_records SET id = id + @offset;
UPDATE ust_partials SET id = id + @offset;
UPDATE ust_access SET id = id + @offset;
UPDATE ust_user_client_ip_label SET id = id + @offset;
UPDATE ust_limits SET id = id + @offset;
UPDATE ust_ab SET id = id + @offset;

-- Foreign keys
UPDATE ust_access SET userid = userid + @offset;
UPDATE ust_user_client_ip_label SET userid = userid + @offset;
UPDATE ust_user_client_watched 
    SET userid = userid + @offset,
        clientid = clientid + @offset;
UPDATE ust_clientpage SET clientid = clientid + @offset;
UPDATE ust_client_tag SET clientid = clientid + @offset;
UPDATE ust_client_event 
    SET clientid = clientid + @offset,
        clientpageid = clientpageid + @offset;
UPDATE ust_clicks SET client = client + @offset;
UPDATE ust_movements SET client = client + @offset;
UPDATE ust_records SET client = client + @offset;
UPDATE ust_partials SET client = client + @offset;

-- === Reset AUTO_INCREMENT for each table ===

-- ust_users
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_users;
SET @sql = CONCAT('ALTER TABLE ust_users AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clients
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clients;
SET @sql = CONCAT('ALTER TABLE ust_clients AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clientpage
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clientpage;
SET @sql = CONCAT('ALTER TABLE ust_clientpage AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_client_tag
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_client_tag;
SET @sql = CONCAT('ALTER TABLE ust_client_tag AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_client_event
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_client_event;
SET @sql = CONCAT('ALTER TABLE ust_client_event AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clicks
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clicks;
SET @sql = CONCAT('ALTER TABLE ust_clicks AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_movements
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_movements;
SET @sql = CONCAT('ALTER TABLE ust_movements AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_records
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_records;
SET @sql = CONCAT('ALTER TABLE ust_records AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_partials
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_partials;
SET @sql = CONCAT('ALTER TABLE ust_partials AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_access
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_access;
SET @sql = CONCAT('ALTER TABLE ust_access AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_user_client_ip_label
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_user_client_ip_label;
SET @sql = CONCAT('ALTER TABLE ust_user_client_ip_label AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_limits
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_limits;
SET @sql = CONCAT('ALTER TABLE ust_limits AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_ab
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_ab;
SET @sql = CONCAT('ALTER TABLE ust_ab AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;

-- Re-enable FK checks
SET FOREIGN_KEY_CHECKS=1;
```