Post reply

I want to truncate table before inserts data

  • December 13, 2011 at 7:44 am

    #261390

    Hello,

    I have remote server and local server

    I have linked remote server

    I normally inserts data

    from local to remote

    using

    INSERT INTO ...

    SELECT ....

    i want to truncate remote table before copying records

    i tried to create trigger on remote server as before insert

    but it doesnt work

    how can i truncate remote table...

    Regards

  • December 13, 2011 at 8:03 am

    #1420635

    Unless you have permission to modify the table (Alter Table permission), you can't truncate. And there's no such thing in SQL Server as a "before trigger". Only "after" and "instead of" triggers.

    Either use Delete, or use a login that has Alter Table permissions, and issue the truncate command as part of the insert script.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 13, 2011 at 8:10 am

    #1420643

    Are you trying to do this all in one statement? It seems like you would truncate or delete the data, then insert the data. Are you doing this with a job or ssis package, meaning is this going to be automated? I would suggest not trying to delete all the rows using a trigger but to come up with a different solution.

    Cliff

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

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