Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INSERTION Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 4:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1519557
Posted Wednesday, December 4, 2013 4:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,315, Visits: 9,732
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
Post #1519563
Posted Wednesday, December 4, 2013 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 7,095, Visits: 6,913
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.

Post #1519595
Posted Wednesday, December 4, 2013 8:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 3,535, Visits: 7,620
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse