INSERTION

  • HI all,

    I have a table TableA with structure:

    CREATE TABle tABLEA

    (

    ID INT,

    SourceData nvarchar(MAX)

    )

    Column source data contains INSERT query.

    I have 2 lakhs records in the table and I want to execute all INSERT statment of column SourceData at once..

    How can I achieve this?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Loop through the table and execute the statements one by one. Either that or write a query to build one set of INSERT statements and execute that in one go. The effect is about the same - either way, you can't run all the INSERTs at once.

    John

  • Depending on the data and insert statements you could parse the insert statements into another table and then insert from there

    Far away is close at hand in the images of elsewhere.
    Anon.

  • To build one set as John said:

    DROP TABLE #TableA

    CREATE TABlE #TableA

    (

    ID INT,

    SourceData nvarchar(MAX)

    )

    INSERT INTO #TableA

    VALUES(1, 'INSERT TableX VALUES( 1''Something'')'),

    (2, 'INSERT TableY VALUES( ''Something Else'', 1)'),

    (3, 'INSERT TableX VALUES( ''Something More'', GETDATE())')

    DECLARE @SQL nvarchar(MAX)

    SELECT @SQL = (SELECT 'EXEC( ''' + REPLACE( SourceData, '''', '''''') + ''')' + CHAR(10)

    FROM #TableA

    FOR XML PATH(''))

    PRINT @SQL

    Reference: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    I suppose that you will have everything double checked before doing this process as some mistake might give you trouble and make the query fail. You should try to work with a single transaction for the whole set of inserts so you can rollback if something fails.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 3 (of 3 total)

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