問題描述

有誰知道我在這里做錯了什么，我有一個從遠程來源獲取貨幣數據的網頁，我獲取數據并通過存儲過程將其插入到 sql 數據庫中.如果我將 truncate 放在 insert 語句的前面，它會截斷表并插入最后一條記錄.如果我刪除截斷，它會插入所有記錄.

Does anyone know what i'm doing wrong here, I have a webpage that gets currency data from a remote source, I get the data and insert it into sql database via a stored procedure. If i put truncate in front of the insert statement, it truncates the table and inserts the last record. If i remove the truncate, it inserts all the records.

即

truncate table tblTablename;

insert into tblTablename
(columns)
values
(data)

上面將插入 289 條記錄中的最后一條記錄.

The above will insert the last record from 289 records.

如果我刪除 truncate，所有 289 條記錄都會插入.

If i remove truncate all 289 records are inserted.

我曾嘗試使用waitfor 1 秒鐘，但也未能奏效.

I have tried using waitfor, for 1 second but that failed to work either.

我不知道還能做什么，所以任何幫助將不勝感激

I'm not sure what else to do, so any help would be appreciated

在網頁中我有一個 foreach 循環

In webpage I have a foreach loop

喬治

/---------------------- SQL 代碼 -----------------

/---------------------- SQL Code -----------------

  ALTER PROCEDURE [dbo].[atSP_InsertCurrency]
-- Add the parameters for the stored procedure here
@CurrencyCountry VarChar(150),
@CurrencyRate VarChar(150),
@UpdateSuccessFail  INT OUTPUT 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
TRUNCATE TABLE [dbo].[at_CurrencyRates];

WAITFOR DELAY '000:00:01'


INSERT INTO [dbo].[at_CurrencyRates]
(
    [CurrencyCode],
    [CurrencyExchangeRate]
)
VALUES
(
    @CurrencyCountry,
    @CurrencyRate
)
IF(@@ROWCOUNT > 0)
    BEGIN
    select  @UpdateSuccessFail = '1'
    END
    ELSE
    BEGIN
    select  @UpdateSuccessFail = '0'
    END
END

推薦答案

如果調用 289 次，則需要將 TRUNCATE TABLE [dbo].[at_CurrencyRates]; 移出存儲過程逐行插入.

You need to move TRUNCATE TABLE [dbo].[at_CurrencyRates]; out of the stored procedure if you are calling it 289 times to insert row by row.

每次調用存儲過程時，它都會從表中刪除所有行，因此您最終只會得到剛剛插入的一行.

Every time you call the stored procedure it deletes all the rows from the table so you will always only end up with the one row that you just inserted.

最好改變存儲過程以一次性插入所有需要的行，而不是一次插入一個.您可以使用表值參數來傳遞所有所需的行，然后您只需要一個 TRUNCATE 后跟一個 INSERT [dbo].[at_CurrencyRates] ... SELECT * FROM @TVP.

Better would be to alter the stored procedure to do the insert of all required rows in one go rather than just one at a time. You can use a table valued parameter to pass in all of the desired rows then you would just need a TRUNCATE followed by an INSERT [dbo].[at_CurrencyRates] ... SELECT * FROM @TVP.

這篇關于截斷表然后將數據插入同一個表只插入 1 條記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！