As @AaronBertrand pointed out in his comment, objects are attached to other objects by object_id, not by object name. Having said that, there is a way to move data from one table to another without the need to use sp_rename.
Since example code is the cleanest way to show how something works, I've created the following test-bed code to illustrate how to use ALTER TABLE ... SWITCH syntax to enable data migration.
First, we'll create an "existing" Product and ProductAudit table, with a trigger on the Product table, that inserts rows into the ProductAudit table:
USE tempdb;
IF OBJECT_ID('dbo.Product') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
ProductKey int NOT NULL
CONSTRAINT PK_Product
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, SomeData varchar(4000) NOT NULL
, TriggerUpdated bit NOT NULL
CONSTRAINT df_Product_TriggerUpdated
DEFAULT (0)
);
GO
IF OBJECT_ID('dbo.ProductAudit') IS NOT NULL
DROP TABLE dbo.ProductAudit;
CREATE TABLE dbo.ProductAudit
(
ProductAuditKey int NOT NULL
CONSTRAINT PK_ProductAudit
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, ProductKey int NOT NULL
, SomeData varchar(4000) NOT NULL
);
GO
CREATE TRIGGER dbo.ProductAuditInsertTrigger
ON dbo.Product
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.ProductAudit (ProductKey, SomeData)
SELECT i.ProductKey
, i.SomeData
FROM inserted i
UPDATE dbo.Product
SET TriggerUpdated = 1
FROM dbo.Product p
INNER JOIN inserted i ON p.ProductKey = i.ProductKey;
END
GO
Here, we'll insert 100,000 rows into the Product table:
;WITH src AS (
SELECT *
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1
CROSS APPLY src s2
CROSS APPLY src s3
CROSS APPLY src s4
CROSS APPLY src s5;
SELECT ProductCount = COUNT(*)
FROM dbo.Product pa
WHERE pa.TriggerUpdated = 1;
SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;
Results of the SELECT queries above:
ProductCount
------------
100000
ProductAuditCount
-----------------
100000
This will create a "temporary" table to hold the rows we want to retain:
IF OBJECT_ID('dbo.ProductAuditNew') IS NOT NULL
DROP TABLE dbo.ProductAuditNew;
CREATE TABLE dbo.ProductAuditNew
(
ProductAuditKey int NOT NULL
CONSTRAINT PK_ProductAuditNew
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, ProductKey int NOT NULL
, SomeData varchar(4000) NOT NULL
);
This code "migrates" the data from the existing table into the new table, retaining the triggers present on the original table, without recreating them. The ALTER TABLE ... SWITCH syntax is a meta-data-only operation that is extremely quick, making this a great way to migrate data. The code below sets the transaction isolation level to lock the tables involved to prevent other processes inserting or updating data in any of the involved tables.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --lock all row-ranges affected by
--the migration
DECLARE @msg varchar(1000);
BEGIN TRANSACTION
BEGIN TRY
--disable the trigger temporarily
DISABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
--copy the rows we want to retain from the old table to the new table
INSERT INTO dbo.ProductAuditNew (ProductKey, SomeData)
SELECT pa.ProductKey
, pa.SomeData
FROM dbo.ProductAudit pa
WHERE pa.ProductAuditKey % 5 = 0; --retain 20% of the rows
--truncate the "old" table
TRUNCATE TABLE dbo.ProductAudit;
--switch the single partition belonging to the "new" table
--into the "old" table
ALTER TABLE dbo.ProductAuditNew
SWITCH TO dbo.ProductAudit;
--remove the "new" table, which is actually empty now
DROP TABLE dbo.ProductAuditNew;
--re-seed the table's identity object; only necessary if an IDENTITY column is present
DBCC CHECKIDENT('dbo.ProductAudit', RESEED);
--re-enable the trigger
ENABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @msg = ERROR_MESSAGE();
PRINT (@msg);
ROLLBACK TRANSACTION
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --return to default mode
SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;
Results:
ProductAuditCount
-----------------
20000
Here, we'll insert another 10 rows into the dbo.Product table to prove the trigger still works:
;WITH src AS (
SELECT *
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1;
SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;
Results:
ProductAuditCount
-----------------
20010
