Post reply

Remove Duplicate Row MSSQL CE 3.5

  • July 7, 2010 at 10:54 am

    #139631

    I am not a DBA, but I am trying to learn, so thanks for your help!

    DATE_TIME is the column and I am trying to delete any rows which have duplicate dates, through importing or whatever. I have primary key restrictions on the column now, but in the past it did not have that restriction. I know about the method of doing the distinct selection, duplicating the table, truncating the old table, inserting the data back and then dropping the temp table. But, I am trying to do it this way, as my tables sometimes are over 100,000 rows. If I can't get the statement below working, I suppose I can do the distinct method.

    Some code before the statement in question:

    string connStr = GetConnectionString(databaseName);

    SqlCeCommand cmd = new SqlCeCommand();

    SqlCeConnection conn = new SqlCeConnection();

    conn.ConnectionString = connStr;

    cmd.Connection = conn;

    conn.Open();

    The statement I am having problems with:

    deleteStr = " Delete FROM " + tableName + " WHERE DATE_TIME IN ( SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TIME ORDER BY DATE_TIME) AS row_id ) WHERE row_id > 1";

    cmd.CommandText = deleteStr;

    cmd.ExecuteNonQuery();

    I keep getting errors on Over, from executenonquery.

    If anyone has any suggestions, It would be greatly appreciated. I am doing this in a C# winform application which I have written, so this all needs to be programmatic, and not done through any other SQL Query application. And being CE it perhaps has other limitations.

    Thanks again, any help would be greatly appreciated.

  • July 7, 2010 at 2:18 pm

    #1189847

    if you are using expressions in your sql task with datetime fields, make sure to use TYPE cast DT_WSTR()

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 7, 2010 at 2:49 pm

    #1189868

    Thanks, I will look into that. However, I don't think that is the issue that I am running into. It says it is having a problem with 'over'

    Regards,

    Merrill

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply