Truncate table then insert data into same table only inserts 1 record

Question

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.

i.e

truncate table tblTablename;

insert into tblTablename
(columns)
values
(data)

The above will insert the last record from 289 records.

If i remove truncate all 289 records are inserted.

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

In webpage I have a foreach loop

George

/---------------------- 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

Please post your actual code. `insert into tblTablename (columns) values (data)` only ever inserts 1 row except if you have multiple items separated by a comma. — Martin Smith, Jan 06 '13 at 13:06
The code you show here adds one record, not 289. Can you post the code that adds 289 records! — Mikael Eriksson, Jan 06 '13 at 13:07

score 5 · Accepted Answer · answered Jan 06 '13 at 13:18

5

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.

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.

answered Jan 06 '13 at 13:18

Martin Smith

438,706
87
741
845

Hi Mark, thanks, I suppose I could do a if exists and update the currency rate, and if they add a new currency, insert that. OR in the webpage create another function that truncates the table 1st then insert the records. – CareerChange Jan 06 '13 at 13:27
Hi Martin thanks, just reading up on table value parameters now, never heard of them until today. – CareerChange Jan 06 '13 at 13:33
@CareerChange - [If you are using C# then example here](http://stackoverflow.com/a/10779567/73226). That also demonstrates the `MERGE` syntax – Martin Smith Jan 06 '13 at 13:36

CareerChange · Answer 2 · 2013-01-07T13:13:30.160

Thought i would add my working source code for any visitor who retrieves json data and then wants to bulk insert the data into sql using MERGE

I'm not a professional coder, i'm learning myself after been made redundant and I must admit the more I learn, the less I know, the code below is from spending hours googling merge yesterday and reading other peoples blogs/posts, to many sites to mention here, but thanks to all those coders to give up their time and blog about how they solve problem with tips and tricks.

The code I have supplied works OK in my project.

 string dbConn = ConfigurationManager.ConnectionStrings["CurrDB"].ConnectionString;
const string strCurrencyCode = "http://SomeRemoteJsonSource.com"; 

public void InsertCurrency()
        {

        WebClient wc = new WebClient();
        var dict = JsonConvert.DeserializeObject<Dictionary<string, string>>(wc.DownloadString(strCurrencyCode));

        string tmpTable = "create table #at_CurrencyCountries (CurrencyCountry varchar(150), CurrencyCountryCode varchar(4))";

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("CurrencyCountry", typeof(string)));
        table.Columns.Add(new DataColumn("CurrencyCountryCode", typeof(string)));

        foreach (var wy in dict.AsEnumerable())
            {
            DataRow row = table.NewRow();
            row["CurrencyCountry"] = wy.Value;
            row["CurrencyCountryCode"] = wy.Key;
            table.Rows.Add(row);
            }

        using (SqlConnection cn = new SqlConnection(dbConn))
            {
            cn.Open();
            SqlCommand cmd = new SqlCommand(tmpTable, cn);
            cmd.ExecuteNonQuery();

            using (SqlBulkCopy bulk = new SqlBulkCopy(cn))
                {
                bulk.DestinationTableName = "#at_CurrencyCountries";
                bulk.WriteToServer(table);
                }
            string mergeSql = "merge into at_CurrencyCountries as Target "
                + "using #at_CurrencyCountries as Source "
                + "on "
                + "Target.CurrencyCountry=Source.CurrencyCountry "
                + "and "
    + "Target.CurrencyCountryCode = Source.CurrencyCountryCode "
                + "when matched then "
                + "update set Target.CurrencyCountryCode=Source.CurrencyCountryCode "
                + "when not matched then "
                + "insert (CurrencyCountry,CurrencyCountryCode) values (Source.CurrencyCountry, Source.CurrencyCountryCode)"
                + "WHEN NOT MATCHED BY SOURCE THEN DELETE;";
            cmd.CommandText = mergeSql;
            cmd.ExecuteNonQuery();               
            cmd.CommandText = "drop table #at_CurrencyCountries";
            cmd.ExecuteNonQuery();
            }
        }

George

Truncate table then insert data into same table only inserts 1 record

2 Answers2

Linked