By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.salesDetail(ID int);
CREATE OR ALTER PROCEDURE dbo.CopyOneTableToAnother @NewTable sysname,
@OldTable sysname,
@NewSchema sysname = NULL,
@OldSchema sysname = NULL AS
BEGIN
SELECT @NewSchema = ISNULL(@NewSchema,default_schema_name),
@OldSchema = ISNULL(@OldSchema,default_schema_name)
FROM sys.database_principals
WHERE name = USER_NAME();
DECLARE @SQL nvarchar(MAX);
SELECT @SQL = N'SELECT * INTO ' + QUOTENAME(@NewSchema) + N'.' + QUOTENAME(@NewTable) + N' FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.name = @OldSchema
AND t.[name] = @OldTable;
--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;
END;
EXEC dbo.CopyOneTableToAnother N'tblDetails', 'salesDetail';
SELECT *
FROM dbo.tblDetails;
| ID |
|---|
DROP TABLE dbo.tblDetails;
DROP TABLE dbo.salesDetail;