截断表然后将数据插入同一个表只插入 1 条记录

Truncate table then insert data into same table only inserts 1 record(截断表然后将数据插入同一个表只插入 1 条记录)
本文介绍了截断表然后将数据插入同一个表只插入 1 条记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

有谁知道我在这里做错了什么,我有一个从远程来源获取货币数据的网页,我获取数据并通过存储过程将其插入到 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 条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)