StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What do you need to do with those entries that do or don't exist in your table??</p> <p>Depending on what you need, maybe the new <code>MERGE</code> statement in SQL Server 2008 could fit your bill - update what's already there, insert new stuff, all wrapped neatly into a single SQL statement. Check it out!</p> <ul> <li><a href="http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx" rel="nofollow noreferrer">http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx</a></li> <li><a href="http://www.sql-server-performance.com/articles/dba/SQL_Server_2008_MERGE_Statement_p1.aspx" rel="nofollow noreferrer">http://www.sql-server-performance.com/articles/dba/SQL_Server_2008_MERGE_Statement_p1.aspx</a></li> <li><a href="http://blogs.msdn.com/brunoterkaly/archive/2008/11/12/sql-server-2008-merge-capability.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/brunoterkaly/archive/2008/11/12/sql-server-2008-merge-capability.aspx</a></li> </ul> <p>Your statement would look something like this:</p> <pre><code>MERGE INTO (your target table) AS t USING (your source table, e.g. a temporary table) AS s ON t.ID = s.ID WHEN NOT MATCHED THEN -- new rows does not exist in base table ....(do whatever you need to do) WHEN MATCHED THEN -- row exists in base table ... (do whatever else you need to do) ; </code></pre> <p>To make this really fast, I would load the "new" records from e.g. a TXT or CSV file into a temporary table in SQL server using BULK INSERT:</p> <pre><code>BULK INSERT YourTemporaryTable FROM 'c:\temp\yourimportfile.csv' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR =' |\n' ) </code></pre> <p>BULK INSERT combined with MERGE should give you the best performance you can get on this planet :-)</p> <p>Marc</p> <p>PS: here's a note from <a href="http://technet.microsoft.com/en-us/library/cc879317.aspx" rel="nofollow noreferrer">TechNet</a> on MERGE performance and why it's faster than individual statements:</p> <p><em>In SQL Server 2008, you can perform multiple data manipulation language (DML) operations in a single statement by using the MERGE statement. For example, you may need to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Typically, this is done by executing a stored procedure or batch that contains individual INSERT, UPDATE, and DELETE statements. <strong>However, this means that the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.</strong> By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. However, performance gains depend on having correct indexes, joins, and other considerations in place. This topic provides best practice recommendations to help you achieve optimal performance when using the MERGE statement.</em></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload