There is always an easy was to create table by having other table using below statement in MS SQL Server.
Select * into T1 from T2 where 1=2
But big problem over here is if any column has BLOB data type(ntext, XML, varbinary) by default it stored in PRIMARY filegroup. If any situation we need to create table in different File group will end up with problem.
and I just solved using this Procedure created by myself.
IF OBJECT_ID('dbo.P_Arc_CreateTable') IS NOT NULL
DROP PROC dbo.P_Arc_CreateTable
GO
Create Proc dbo.P_Arc_CreateTable
(@SourceTableName nvarchar(100)
,@TargetTableName nvarchar(100)
,@FileGroup nvarchar(20)=NULL
,@PartitionSchemeFG nvarchar(200)=NULL
)
as
BEGIN
Declare @TableName as nvarchar(100)
DEclare @SQL as nvarchar(max)
Declare @ShemaName as nvarchar(10)
Declare @ErrorMessage as nvarchar(1000)
IF EXISTS (Select 1 from sys.columns where object_id = object_ID(@SourceTableName) and is_ansi_padded = 1)
SET @SQL = ' SET ANSI_PADDING ON ' +char(10)
ELSE
SET @SQL = ' SET ANSI_PADDING OFF ' +char(10)
SET @ShemaName = (Select SCHEMA_NAME(t.SCHEMA_ID) from sys.tables t where name = @SourceTableName)
SET @SQL = @SQL + ' CREATE TABLE ['+@ShemaName+'].['+@TargetTableName+']('+ char(10)
Select
@SQL = @sql
+ '['+C.COLUMN_NAME + '] [' + C.DATA_TYPE +']'
+ CASE WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' ('+ rtrim(cast(C.CHARACTER_MAXIMUM_LENGTH as CHAR))+')' ELSE '' END
+ CASE WHEN Cl.user_type_id in (106,108) THEN ' ('+ rtrim(cast(C.NUMERIC_PRECISION as CHAR))+','+RTRIM(CAST( c.NUMERIC_SCALE AS CHAR))+ ')' ELSE '' END
+ case when cl.is_identity = 1 THEN ' IDENTITY(1,1) ' ELSE '' END
+ CASE WHEN len(C.COLUMN_DEFAULT )> 0 THEN ' DEFAULT ' + rtrim(CAST(C.COLUMN_DEFAULT AS CHAR)) ELSE '' END--THEN ' DEFAULT ' + c.COLUMN_DEFAULT ELSE '' END
+ ' ' + CASE WHEN C.COLLATION_NAME IS NOT NULL THEN ' COLLATE '+ C.COLLATION_NAME ELSE '' END
+ ' '+ CASE WHEN C.IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+',' + CHAR(10)
from INFORMATION_SCHEMA.COLUMNS C
inner join sys.columns cl
on cl.object_id = object_id(c.Table_Name)
and cl.name = c.COLUMN_NAME
where Table_Name = @SourceTableName
order by c.ORDINAL_POSITION
SET @SQL = Substring(@SQL,0,Len(@SQL)-1)
IF (@PartitionSchemeFG IS NOT NULL AND @PartitionSchemeFG <> '')
SET @SQL = @SQL + char(10)+ ') ON '+@PartitionSchemeFG
ELSE
SET @SQL = @SQL + char(10)+ ') ON['+@FileGroup+ ']'
BEGIN TRY
IF OBJECT_ID(@TargetTableName) IS NOT NULL
BEGIN
PRINT 'Table - ' + @TargetTableName + ' already exists.'
RETURN
END
ELSE
BEGIN
EXEC (@SQL)
--print @SQL
PRINT 'Created Table - ' + @TargetTableName + ' successfully.'
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
PRINT 'Error: In creating table - '+ @TargetTableName+'.'
RAISERROR(@ErrorMessage,16,1)
END CATCH
--Print @SQL
END
--dbo.P_Arc_CreateTable @SourceTableName = 'PromotionInstance',@TargetTableName = 'PromotionInstance_SWD' ,@FileGroup='FG1'
GO
It supports creating an table in Primary File Group, user defined filegroup or in Partition Schema.
Happy Programming ..:)
