Sql-server – the most efficient way of inserting 199 millon rows of data

sql-server-2005

I have a 2.2 GB text file in the format of:

1234567890
1234567890
etc.......

I need to insert each line into a table (field 1) such as:

CREATE TABLE [dbo].[table](
    [field1] [varchar](10) NOT NULL,
    [field2] [varchar](3) NOT NULL

What is the most efficient way of inserting this data?
Is there a premade program that can be used? (i.e can it be done from the command line externally)

I've created a script and I select 1,000 records create a sql query around them and then insert. This takes a while, is there a better way? (Better as in easier, faster, less overhead)

Note: I've tried 1M, 100,000, 10,000 batch sizes, but the query is long at those sizes, so am stuck with 1k for now.

(query used)

INSERT INTO table (field1, field2)
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
// etc....

Best Answer

BCP is likely to be the best tool for the task. SQLFool has a good BCP basics article on its usage.

@Marian posted a comprehensive list of BCP optimisation references in response to a question I asked, which you may find useful.

Related Solutions

SQL Server 2005 – Most Efficient Way to Return Multiple Aggregates in a Single Stored Procedure

COUNT..CASE is the usual way. For "Sent" though it's bit trickier because it's a different uid filter which requires 2 queries on the Message table

I've used this construct to allow for zero row for to/from. It avoids an OR and adding a uid filter to each CASE.

Each derived table (can write as a CTE) will return only one row and I've used a MAX on uid to avoid GROUP BY too in case anyone wonders

SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid] 
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

Edit:

An aggregate without GROUP BY will always return a result (see SO here). I forgot that.

I've updated my SQL

Sql-server – Table structure for many custom profiles

It always pains me to suggest it but theres a reasonable case for making use of an XML column here.

Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.

Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.

If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.

Best Answer

Related Solutions

SQL Server 2005 – Most Efficient Way to Return Multiple Aggregates in a Single Stored Procedure

Sql-server – Table structure for many custom profiles

Related Question