Technical Article

Dynamic Table Name on Select Into

,

You would use this script if you wanted a regular version of table data creating into its own table, if you wanted to track changes etc. Firstly, set your first 3 variables: @DatabaseName, @SchemaName, @SelectFromTableName VARCHAR Variables wtih the relevant values.  There are already values in here, so change them to what you want them to be.  To set this to run on a regular basis, you might want to paste this code in a stored procedure and schedule it on a SQL Agent Job.  A possible way to extend this script is to add a where clause to the select statement, so that you are only selecting the data where a date field in the table is for current day.  Please contact if you have any comments or suggestions

DECLARE @DatabaseName VARCHAR(50)
DECLARE @SchemaName VARCHAR(50)
DECLARE @SelectFromTableName VARCHAR(50)

SET @DatabaseName='Sales'--database you want to read from and create new table in
SET @SchemaName='dbo'--schema you want to read from and create new table under
SET @SelectFromTableName='Customers'--table you want to read from

DECLARE @Sql VARCHAR(500)
DECLARE @TableNamePrefix VARCHAR(20)='Customers'---replace this string with what you want to name your new table
DECLARE @TableNameSuffix VARCHAR(10) = CONVERT(VARCHAR, CAST(GETDATE() AS DATE), 105)
DECLARE @TableName VARCHAR(31)=@TableNamePrefix + '_' + @TableNameSuffix

BEGIN TRY
SET @Sql=
'
  SELECT * INTO '
   + @DatabaseName + '.' + @SchemaName + '.[' + @TableName + '] FROM '
   + @DatabaseName + '.' + @SchemaName + '.' + @SelectFromTableName
EXEC(@sql);

PRINT 'You have created table ' + @TableName + ' with ' + CAST(@@RowCount as VARCHAR) + ' Rows';
END TRY
BEGIN CATCH
IF (ERROR_NUMBER()=208) --invalid object name
BEGIN
PRINT 'You need to specify a valid database table';
END
ELSE
IF (ERROR_NUMBER()=2714)--object you are trying to create already exists
BEGIN
PRINT 'Table ' + @TableName + ' already exists';
END
ELSE
BEGIN
PRINT ERROR_MESSAGE();
END
END CATCH

Rate

3.25 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.25 (4)

You rated this post out of 5. Change rating