using SQL Server, and you want to import 20+ tables from Production into UAT in one go, here’s a streamlined process using SQL Server Management Studio (SSMS).

 

Goal: Import All Tables from Production into UAT (One Time)

🔧 Method 1: Generate Scripts (Schema + Data)

This is the cleanest way to export everything from Production and re-create it in UAT.

🔁 Steps:

1. Open SSMS and connect to your Production DB.

2. Right-click on the Production database > go to:

   Tasks > Generate Scripts
   

3. Select "Specific database objects" → Choose all the tables (you can multi-select).

4. Click Next until you reach Set Scripting Options:

   • Click Advanced

   • Set “Types of data to script” to:

     Schema and Data
     

5. Choose output:

   • Save to file or Clipboard (recommended: file for large exports).

6. Finish the wizard — this generates a .sql script file with:

   • All table definitions

   • All data insert statements

7. Switch to your UAT database in SSMS.

8. Run the generated script in UAT.

---

🧪 Method 2: Import Data via SSMS Import Wizard (Only Data)

If your UAT already has the same tables and you only want to copy the data, use this.

🔁 Steps:

1. Open SSMS.

2. Connect to the UAT database.

3. Right-click UAT DB > go to:

   Tasks > Import Data
   

4. In the Import Wizard:

   • Source: Select SQL Server as source, point it to Production DB.

   • Destination: Point to UAT DB.

5. Choose Copy data from one or more tables.

6. Select the tables (you can check all 20+).

7. Review column mappings (optional).

8. Run the import.

✅ This copies data only, not schema.

---

🧠 Tip: Use Linked Server (Advanced Option)

If this is something you'll do repeatedly, consider setting up a linked server between UAT and Production and then use:

INSERT INTO UATDB.dbo.TableName
SELECT * FROM [ProdServer].[ProdDB].dbo.TableName

---

📌 Summary

Method	Schema	Data	Tools Required
Generate Scripts	✅	✅	SSMS
Import Wizard	❌	✅	SSMS
Linked Server + SQL Insert	❌	✅	SSMS + setup

---

Would you like a sample script or wizard screenshots to guide you through this?

You said:

Want to uat to production only selected db

ChatGPT said:

Thanks — now you're saying the opposite of before:

✅ You want to copy selected databases (or specific tables) from UAT → Production, not the other way around.

---

✅ Goal: Deploy Selected Tables (or Data) from UAT to Production in SQL Server

You can do this safely and selectively, but it depends whether you want to copy:

1. Table schema only (table definitions)

2. Data only

3. Both schema + data

4. Stored procedures, views, etc.

I'll guide you through the best method using SQL Server Management Studio (SSMS).

---

🧰 Option 1: Use Generate Scripts Wizard (Recommended for small sets)

Works for: Specific tables, stored procs, data, etc.

🔁 Steps:

1. In SSMS, connect to UAT database.

2. Right-click the UAT DB > go to:

   Tasks > Generate Scripts
   

3. Choose “Select specific database objects”

   • Pick only the tables, procs, etc. you want to copy.

4. Click Next, then go to Advanced.

   • Set:

     • Types of data to script = Schema and Data

     • Script DROP and CREATE = CREATE only (unless you want to overwrite)

5. Save the script to a .sql file.

6. Open SSMS, connect to Production, and run the script carefully.

✅ This way, only selected objects are copied to Production.

---

⚙️ Option 2: Use Export Data Wizard (Data Only)

Use when UAT & Production already have the same schema, and you only want to move data.

1. In SSMS, right-click the UAT database > go to:

   Tasks > Export Data
   

2. Choose:

   • Source = UAT

   • Destination = Production

3. Choose tables to copy

4. Run the wizard.

✅ This will copy only data into production tables (be cautious — it can overwrite!).

---

🛡️ Important Notes

• NEVER run directly in Production without:

  • Backup

  • Testing the script in a staging environment

• Use transactions when running custom scripts:

BEGIN TRANSACTION

-- Your insert/update/delete statements

-- COMMIT only if verified
COMMIT

-- Or rollback if issue
-- ROLLBACK

---

🧪 Option 3: Custom Script to Copy UAT Data to Production

If you only want a few tables:

-- From UAT DB to Production DB
-- Make sure both DBs are on same SQL Server

INSERT INTO [ProductionDB].[dbo].[YourTable]
SELECT * FROM [UATDB].[dbo].[YourTable]

Or, if on different servers, set up a linked server, then:

INSERT INTO ProdServer.ProductionDB.dbo.YourTable
SELECT * FROM UATServer.UATDB.dbo.YourTable

---

🔐 Final Checklist Before Updating Production

✅ Review all scripts
✅ Take production backup
✅ Test in staging/UAT
✅ Use transactions
✅ Inform stakeholders