We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.
Yet we should not pass up our opportunities in that critical 3%." Knuth 1974 etc.
We use both BULK INSERT and MERGE statements as this implementation not only used for inserting the records, also for updating records if already exist.
So the use case will be as below – Note: This implementation will work only from SQL Server 2008 and later versions (as MERGE statement introduced from SQL Server 2008).
I have a three column table with just over 6 Billion rows in SQL Server 2008 R2.
F., NULL,5022, Mexico,(5) 555-3392,(5) 555-7293CREATE PROCEDURE SP_Import Customer Data AS BEGIN -- Creating a Temproary Table for importing the data from csv file. CREATE TABLE #Customers( [Customer ID] [nchar](5) NOT NULL, [Company Name] [nvarchar](40) NOT NULL, [Contact Name] [nvarchar](30) NULL, [Contact Title] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [Postal Code] [nvarchar](10) NULL, [Country] [nvarchar](15) NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL, CONSTRAINT [PK_Customers1] PRIMARY KEY CLUSTERED ( [Customer ID] ASC ) ); -- Inserting all the from csv to temproary table using BULK INSERT BULK INSERT #Customers FROM 'D:\Blog\Import Data\Customer Import.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); -- Selecting the records from temproary table. -- SELECT * FROM #Customers; -- By using MERGE statement, inserting the record if not present and updating if exist. Contact Title, Source Table.[Address], Source Table. When I first started with SQL Server, as initially I worked exclusively with Oracle databases, I found myself constantly thinking in terms of Oracle datatypes and having to look up what the equivalent was in SQL Server.Could you please tell me, whether it was a good idea? Or do you know how many records (more or less) can be stored in a table before performance is lowered significantly?"Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered.