CODEWITHSUNDEEP
sql
nikki
nikki

Reputation: 105

Create a new column as num containing values of the table name

I have two tables in SQL and want to create a new column from the table name.

In the database, there are many many tables and name are following similar pattern e.g. total_abc_001, total_abc_0002etc... I want only tables starting with total_abc to be selected..

And create a new column as num which contains the values of table name last characters like 001, 002.

I have tried like this:

DROP TABLE IF EXISTS TABLE_NEW_XY    

CREATE TABLE TABLE_NEW_XY 
(
     email VARCHAR(MAX),
     Profile VARCHAR(MAX)
)


DECLARE @Sql NVARCHAR(MAX) = '',
        @TableName VARCHAR(MAX),
        @Id INT

 DECLARE Table_Cursor CURSOR FOR 
     SELECT 
         ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id,
         TABLE_NAME 
     FROM 
         INFORMATION_SCHEMA.TABLES 
     WHERE 
         TABLE_TYPE = 'BASE TABLE' 
         AND TABLE_NAME LIKE 'total_abc_%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id, @TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF (@Id = 1)
    BEGIN
        SET @Sql = @Sql + 'SELECT email, Profile FROM '+@TableName 
        SELECT @SQL
    END
    ELSE
    BEGIN
        SET @Sql = @Sql + ' UNION ALL SELECT email, Profile FROM '+@TableName --Modify the columns based on your column names
    END

    FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
 END 

 CLOSE Table_Cursor  
 DEALLOCATE Table_Cursor

 INSERT INTO TABLE_NEW_XY
 EXEC (@Sql)

Now i wanted to add a new column "num in the dynamic sql itself to get the values of the filename in the column as 001,002,003 and so on.

Can you please suggest how to achieve this?

Upvotes: 0

Views: 56

Answers (1)

Simeon
Simeon

Reputation: 836

If the number is always 3 digits on the end of the @TableName you can let your dynamic sql hardcode it:

    IF (@Id = 1)
    BEGIN
        SET @Sql = @Sql + 'SELECT email, Profile, ''' + RIGHT(@TableName,3) + ''' tname FROM '+@TableName 
        SELECT @SQL
    END
    ELSE
    BEGIN
        SET @Sql = @Sql + ' UNION ALL SELECT email, Profile, ''' + RIGHT(@TableName,3) + ''' tname FROM '+@TableName --Modify the columns based on your column names
    END

This should output:

SELECT email, Profile, '001' tname FROM total_abc_001
UNION ALL SELECT email, Profile, '002' tname FROM total_abc_002

If the n last characters you want to capture are variable you can use the same principle with some more complex find/replace functions

Upvotes: 1

Related Questions