ReRunSQL

← Back to Generator

How to Make SQL Scripts Rerunnable

Learn how ReRunSQL transforms common SQL operations into idempotent, safe-to-rerun scripts with intelligent existence checks and safeguards.

How do I make a CREATE TABLE statement rerunnable?

When you run a CREATE TABLE statement twice, it fails with "table already exists". ReRunSQL adds an existence check to prevent this error.

❌ Original (fails on rerun)
CREATE TABLE Users (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(255)
);
✅ Idempotent version (safe to rerun)
-- Check if table exists before creating
IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'Users'
)
BEGIN
    CREATE TABLE Users (
        Id INT PRIMARY KEY,
        Name NVARCHAR(100),
        Email NVARCHAR(255)
    );
END;
✓ Benefit: Run the script multiple times in CI/CD pipelines without errors. Perfect for automated deployments.

How do I make ALTER TABLE ADD COLUMN rerunnable?

Adding a column that already exists causes an error. ReRunSQL checks if the column exists before attempting to add it.

❌ Original (fails if column exists)
ALTER TABLE Users 
ADD PhoneNumber NVARCHAR(20);
✅ Idempotent version
-- Add column only if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'Users' 
    AND COLUMN_NAME = 'PhoneNumber'
)
BEGIN
    ALTER TABLE Users 
    ADD PhoneNumber NVARCHAR(20);
END;
✓ Benefit: Safe schema migrations. No more "column already exists" errors when running migration scripts multiple times.

How do I make CREATE INDEX idempotent?

Creating an index twice fails with "index already exists". ReRunSQL adds a check to skip creation if the index exists.

❌ Original
CREATE INDEX IX_Users_Email 
ON Users(Email);
✅ Idempotent version
-- Create index only if it doesn't exist
IF NOT EXISTS (
    SELECT * FROM sys.indexes 
    WHERE name = 'IX_Users_Email' 
    AND object_id = OBJECT_ID('Users')
)
BEGIN
    CREATE INDEX IX_Users_Email 
    ON Users(Email);
END;
✓ Benefit: Rerun performance optimization scripts without errors. Great for staging/production parity.

How do I prevent duplicate inserts when rerunning scripts?

Running INSERT statements multiple times creates duplicate data. ReRunSQL converts them to conditional inserts or MERGE/UPSERT patterns.

❌ Original (creates duplicates)
INSERT INTO Users (Id, Name, Email) 
VALUES (1, 'John Doe', '[email protected]');
✅ Idempotent version (SQL Server)
-- Insert only if record doesn't exist
IF NOT EXISTS (SELECT 1 FROM Users WHERE Id = 1)
BEGIN
    INSERT INTO Users (Id, Name, Email) 
    VALUES (1, 'John Doe', '[email protected]');
END;
✅ Alternative: MERGE (upsert pattern)
MERGE Users AS target
USING (SELECT 1 AS Id, 'John Doe' AS Name, '[email protected]' AS Email) AS source
ON target.Id = source.Id
WHEN NOT MATCHED THEN
    INSERT (Id, Name, Email) 
    VALUES (source.Id, source.Name, source.Email);
✓ Benefit: Seed data scripts that are safe to rerun. No duplicate test data or reference data.

How do I make DROP TABLE safe to rerun?

Dropping a table that doesn't exist causes an error. ReRunSQL adds an existence check before dropping.

❌ Original
DROP TABLE TempData;
✅ Idempotent version
-- Drop table only if it exists
IF EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'TempData'
)
BEGIN
    DROP TABLE TempData;
END;
✓ Benefit: Cleanup scripts that never fail. Perfect for rollback scenarios and testing.

How do I make ALTER TABLE DROP COLUMN rerunnable?

Dropping a column that doesn't exist causes an error. ReRunSQL checks column existence first.

❌ Original
ALTER TABLE Users 
DROP COLUMN MiddleName;
✅ Idempotent version
-- Drop column only if it exists
IF EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'Users' 
    AND COLUMN_NAME = 'MiddleName'
)
BEGIN
    ALTER TABLE Users 
    DROP COLUMN MiddleName;
END;
✓ Benefit: Safe schema cleanup. Remove deprecated columns without breaking existing environments.

How do I make stored procedure creation idempotent?

Creating a procedure that exists fails. ReRunSQL uses DROP IF EXISTS or CREATE OR ALTER patterns.

❌ Original
CREATE PROCEDURE GetUserById
    @UserId INT
AS
BEGIN
    SELECT * FROM Users WHERE Id = @UserId;
END;
✅ Idempotent version (SQL Server 2016+)
-- Create or replace procedure
CREATE OR ALTER PROCEDURE GetUserById
    @UserId INT
AS
BEGIN
    SELECT * FROM Users WHERE Id = @UserId;
END;
✅ Alternative (older SQL Server)
-- Drop if exists, then create
IF EXISTS (SELECT * FROM sys.objects 
           WHERE type = 'P' AND name = 'GetUserById')
BEGIN
    DROP PROCEDURE GetUserById;
END;
GO

CREATE PROCEDURE GetUserById
    @UserId INT
AS
BEGIN
    SELECT * FROM Users WHERE Id = @UserId;
END;
✓ Benefit: Deploy stored procedure updates repeatedly without manual cleanup.

What SQL statements does ReRunSQL support?

ReRunSQL intelligently transforms the most common DDL and DML operations:

✓ Use cases: CI/CD pipelines, database migrations, schema drift fixes, seed data scripts, rollback scenarios, multi-environment deployments.

Ready to Make Your SQL Scripts Rerunnable?

Transform your SQL scripts in seconds with intelligent existence checks and safeguards

Try ReRunSQL Now →