Creating table from a Query results

  • Hi

    I have this query;

    select c.name

    FROM sys.tables AS t

    INNER JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0

    order by column_id

    and I get these results;

    NOTE_ID

    NOTE_DESCRIPTION

    NOTE_TEXT

    NOTE_STATUS

    NOTE_STARTDATE

    NOTE_ENDDATE

    NOTE_AUTHOR

    NOTE_LASTUPDATE

    I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.

    I’m trying to create it but no luck. Help please, or advise.

  • Can't you use SELECT ... INTO?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I want to create a new table with the about of that query, that output has to be my columns

  • hoseam (2/6/2014)


    I want to create a new table with the about of that query, that output has to be my columns

    Ah ok, now I get it.

    DECLARE @SQLStatement VARCHAR(2000);

    SET @SQLStatement = 'CREATE TABLE Hosea_tblDATA_NOTES (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name + ' VARCHAR(100) NULL'

    FROM sys.tables t

    JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

    WHEREt.name= 'Hosea_tblDATA_NOTES'

    AND c.is_identity= 0

    ORDER BY column_id;

    SET @SQLStatement = @SQLStatement + ');';

    SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma

    EXEC sp_executesql @SQLStatement;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Is that a dynamic sql, I'm just reading about it now and I haven't grasp it yet, can you explain your query to me please, also explaining dynamic sql.

    I copied the code and I get this error, "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'"

  • It is dynamic SQL yes.

    The concept is simple: you construct a SQL statement on the fly, store it inside a string variable and execute it with sp_executesql.

    Add PRINT @SQLStatement right before the EXEC statement, you'll see what I mean.

    To resolve the error: make @SQLStatement a NVARCHAR variable.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I tried this for a table with VARCHAR and DECIMAL datatype. and came up with following ... not sure whether it will work for all the datatypes out there or not ... but you can give it a try

    select t.name TAB_NAME,'[' + c.name +']' COL_NAME,TY.name DT_NAME,c.max_length,c.precision,c.scale

    INTO #TEMP

    FROM sys.tables AS t

    INNER JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    LEFT OUTER JOIN sys.types TY

    ON c.system_type_id = TY.system_type_id

    where t.name= 'TAB_NAME'and c.is_identity = 0

    order by column_id

    DECLARE @tab VARCHAR(1000)= 'CREATE TABLE NEW_TAB_NAME ( '

    SELECT @tab +

    STUFF(

    (

    SELECT ','+ COL_NAME + ' ' + CASE

    WHEN DT_NAME LIKE '%CHAR%' THEN DT_NAME + '(' + CONVERT(VARCHAR(4),max_length) + ')'

    WHEN DT_NAME = 'DECIMAL' THEN DT_NAME + '(' + CONVERT(VARCHAR(4),PRECISION) +',' + CONVERT(VARCHAR(4),SCALE) +')'

    ELSE DT_NAME

    END

    FROM #TEMP

    FOR XML PATH ('')

    ),1,1,''

    ) + ')'

    DROP TABLE #TEMP

  • hoseam (2/6/2014)


    I want to create a new table with the about of that query, that output has to be my columns

    maybe

    select NOTE_ID, NOTE_DESCRIPTION, NOTE_TEXT, NOTE_STATUS, NOTE_STARTDATE

    , NOTE_ENDDATE, NOTE_AUTHOR, NOTE_LASTUPDATE into NEW_TABLE_NAME

    from Hosea_tblDATA_NOTES

    where 1=0

  • I edited your query to this:

    DECLARE @SQLStatement NVARCHAR(2000);

    SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tblDATA_NOTE (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+st.name +' '+cast(c.max_length as varchar(50))

    FROM sys.tables t

    JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    INNER JOIN sys.types st

    ON st.system_type_id = c.system_type_id

    WHERE t.name = 'Hosea_tblDATA_NOTES'

    AND c.is_identity= 0

    ORDER BY column_id;

    SET @SQLStatement = @SQLStatement + ');';

    SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma

    --EXEC sp_executesql @SQLStatement;

    select @SQLStatement

    I get the datatype from sys.types st, now what I'm struggling with is, I want to remove length on INT and DECIMAL but keep it on varchar, and also put it in brackets.

  • SrcName (2/6/2014)


    hoseam (2/6/2014)


    I want to create a new table with the about of that query, that output has to be my columns

    maybe

    select NOTE_ID, NOTE_DESCRIPTION, NOTE_TEXT, NOTE_STATUS, NOTE_STARTDATE

    , NOTE_ENDDATE, NOTE_AUTHOR, NOTE_LASTUPDATE into NEW_TABLE_NAME

    from Hosea_tblDATA_NOTES

    where 1=0

    Yes, hardcoding everything is surely the solution.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • hoseam (2/6/2014)


    I edited your query to this:

    DECLARE @SQLStatement NVARCHAR(2000);

    SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tblDATA_NOTE (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+st.name +' '+cast(c.max_length as varchar(50))

    FROM sys.tables t

    JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    INNER JOIN sys.types st

    ON st.system_type_id = c.system_type_id

    WHERE t.name = 'Hosea_tblDATA_NOTES'

    AND c.is_identity= 0

    ORDER BY column_id;

    SET @SQLStatement = @SQLStatement + ');';

    SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma

    --EXEC sp_executesql @SQLStatement;

    select @SQLStatement

    I get the datatype from sys.types st, now what I'm struggling with is, I want to remove length on INT and DECIMAL but keep it on varchar, and also put it in brackets.

    Stuff like this can very easily be found on the net.

    After 1 minute of googling:

    http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • please tell me if this logic is correct. I'm passing table name as parameter, the same tableName that will be used to create the *new table* called tempTable.

    after the temp table is created, I want to load all the data from the table that I passed in as a parameter, load them into the new tempTable, do some update in the new tempTable then load the data back to the original table, the one I passed in as a parameter.

    Currently I get this error: Must declare the table variable "@TableName"

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTable]') AND type in (N'U'))

    DROP TABLE [dbo].[tempTable]

    GO

    DECLARE @SQLStatement NVARCHAR(2000),@TableName VARCHAR(50), @Product_Id VARCHAR(50), @Fund_Id VARCHAR(50),

    @NewFund_Id VARCHAR(50),@NewProduct_Id VARCHAR(50)

    SET @Product_Id = 'AGP1'

    SET @Fund_Id = 'E016'

    SET @NewProduct_Id = 'PCCV'

    SET @NewFund_Id = 'E33333'

    SET @TableName = 'Hosea_tblDATA_NOTES'

    SET @SQLStatement = 'CREATE TABLE dbo.tempTable (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE

    WHEN st.name LIKE '%CHAR%'

    THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'

    ELSE st.name

    END

    FROM sys.tables t

    JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    INNER JOIN sys.types st

    ON st.system_type_id = c.system_type_id

    WHERE t.name = @TableName

    AND c.is_identity= 0

    ORDER BY column_id;

    SET @SQLStatement = @SQLStatement + ');';

    SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma

    EXEC sp_executesql @SQLStatement;

    SELECT *

    INTO tempTable

    FROM @TableName

    WHERE (Product_Id = is null or Product_Id = @Product_Id )

    AND (FUND_ID is null or FUND_ID = @FUND_ID )

  • You used @tablename inside the dynamic SQL.

    If you would print out @SQLStatement, you would see there is no declaration for @tablename.

    You have two options:

    * pass @tablename as an input parameter to sp_executesql. More info: Using sp_executesql

    * you can use REPLACE on @SQLStatement to replace the string @tablename inside @SQLStatement with the actual value of @tablename. Make sure you don't forget to include single quotes in the @SQLStatement around @tablename.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply