[SQL Server] How to speed insert process up

Question

Hi
I insert rows, which is changed some data, into same table with using loop.
I tried to use while loop, but it's too slow.
How to speed this process up except "bulk insert"?
※because "bulk insert" is needed to data file.

[SQL Code]

DECLARE @ID NUMERIC(10),
                @NAME NUMERIC(10),
                @INFO VARCHAR(30)
BEGIN
    EXEC ( 'DECLARE Cur FOR ' + @cursor)
    OPEN Cur
    FETCH NEXT FROM Cur INTO @ID, @NAME, @INFO
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ID = 2
        INSERT INTO A VALUES(@ID, @NAME, @INFO)
        FETCH NEXT FROM Cur INTO @ID, @NAME, @INFO
    END
END

[Process]

1. Get a row(a) from Table A
2. Set a new row(b) based on a row(a)
   ex) ID NAME INFO
   a 1 Test Data
   b 2 Test Data
3. Insert a new data(b) into Table A
4. Loop process 1 to 3

[SQL Server]
SQL Server 2012 later version

Answer 1

Q : Is this simple select statement?
A : Yes, "@cursor" is select statement.

Q : Does it change every time it is called?
A : Yes, it does.

Q: What is the source?
A : Table that I insert new data. It's same table which is called.

So do:

INSERT INTO A (col1, col2, col3)
   EXEC(@cursor)

I recommend that you always include a column list in the INSERT statement, so it doesn't break if you add one more nullable column to the table.

Answer 2

Hi，@insoo song

Welcome to Microsoft T-SQL Q&A Forum!

In my opinion, I recommend you to use BULK INSERT, which allows you to import data from a data file into a table or view. You can specify the format of the imported data based on how the data is stored in the file.
First, you need to filter out the modified data and save it in csv format, then try the following operations：

USE <database name>  
GO  
BULK INSERT <table name>  
FROM <path to CSV file>  
WITH (FORMAT = 'CSV'  
      , FIRSTROW=2  
      , FIELDQUOTE = '\'  
      , FIELDTERMINATOR = ';'  
      , ROWTERMINATOR = '0x0a');  

Here is an article you can refer to.
Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I tried to use while loop, but it's too slow.

RDBMS are not intendent to work iterative = "loops"/cursor, but set base.
There is nothing for a loop to speed up.
Re-think your process design to work sat based.

Answer 4

Why a cursor in the first place? Just try:

INSERT tbl(...)
    SELECT ...
    FROM  ...

What's wrong with that?

If you really want to keep your loop, you can still speed it up by wrapping the whole thing in BEGIN TRANSACTION and COMMIT TRANSACTION. Also, add SET NOCOUNT ON in the beginning of the script.

But loops is something you only use very occasionally in SQL. Normally you use set-based statements and work with all data at once.

Answer 5

Apparently you are doing some kind of dynamic query.

What is the value of "@cursor"? Is this is simple select statement? Does it change every time it is called? What is the source? Is the data already in a table?

You always want to use bulk or "set" operations in a database, not loops. Without knowing more about what you are doing it is hard to give you direction.