Post reply

Create a table using script

  • April 11, 2014 at 3:39 am

    #306550

    Hi,

    I have a create table script as .txt file. How can i create table using that script through sql query. Plz any one suggest some approach. I heard through openrow set it can be achieved. But how to do.

    Thanks

  • April 11, 2014 at 4:08 am

    #1705178

    I tried it by writting below code

    CREATE TABLE #TEMP

    (

    SCRIPT VARCHAR(4000)

    )

    INSERT INTO #TEMP

    EXEC XP_CMDSHELL 'type filefath\filename.txt'

    DECLARE @SQL VARCHAR(8000)

    SELECT @SQL= STUFF((

    SELECT CONVERT(VARCHAR(1000),' '+ SCRIPT) FROM #TEMP

    FOR XML PATH('')),1,1,'')

    EXEC (@SQL)

    DROP TABLE #temp

  • April 11, 2014 at 4:26 am

    #1705187

    Why try to open it through a SQL query? Why not just use a command line utility like PowerShell or sqlcmd.exe? Either of those can read the file and execute it against a database. That's much more efficient than trying to open files through T-SQL.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 11, 2014 at 4:33 am

    #1705191

    ahhh :crazy:... Indeed... Silly me ... Please try doing the way Grant has proposed...

  • April 11, 2014 at 4:46 am

    #1705192

    Thanks for your reply is there any way to achieve this using open row set

  • April 11, 2014 at 4:58 am

    #1705202

    I'm not sure. It's not something I would try to do. What's the purpose of this approach?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 11, 2014 at 5:36 am

    #1705212

    I guess vinodhkumargv is looking for simple solution and could be...

    Copy .txt code and past it to the new query windows and execute it. It will create the table.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • April 11, 2014 at 6:42 am

    #1705243

    :-D:-):Wow:

Viewing 8 posts - 1 through 7 (of 7 total)

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