CODEWITHSUNDEEP
ssis
Boris Callens
Boris Callens

Reputation: 93327

Truncate tables?

I'm trying to create an Integration Service project that will use a set of existing (legacy) databases to create a new database. As I'm a SSIS noob, my progress is rather incremental and adding a truncate on the entire target database would help me test it more easily.

Is such a thing possible?

Upvotes: 1

Views: 3022

Answers (4)

E Ciotti
E Ciotti

Reputation: 4953

Here a Doctrine 1.2 code to empty all the tables, ignoring innoDB foreign KEYS. I'm using it to bootstrap unit tests

/**
 * Empty all tables, ignoring innoDB foreign keys
 */
function emptyTables()
{
    $conn = Doctrine_Manager::getInstance()->getCurrentConnection();
    $stmt = $conn->prepare('SHOW TABLES');
    $stmt->execute();

    $queries = "SET FOREIGN_KEY_CHECKS = 0;";
    foreach($stmt->fetchAll() as $table) {
        $queries .= "TRUNCATE  `".array_shift($table)."`;\n";
    }
    $queries .= "SET FOREIGN_KEY_CHECKS = 1;";

    $conn->getDbh()->query($queries);
}

Upvotes: 0

user232682
user232682

Reputation: 41

Why use such a complicated SQL statements? Why not just use a Execute SQL Task with an expression variable with value of (DELETE FROM tablename WHERE ControlKey = ?)

Upvotes: 0

Mutation Person
Mutation Person

Reputation: 30498

This SQL statement will do it for you. If you want it to be part of an IS project then make it the SQL of a SQL Script task.

declare @object_id int
declare @str varchar(255)

select @object_id = min(object_id) 
from sys.tables
where name <> 'sysdtslog90'

while @object_id is not null
begin
    select @str = 'truncate table ' + name
    from sys.tables
    where object_id = @object_id

    print @str
    exec (@str)

    select @object_id = min(object_id) 
    from sys.tables
    where object_id > @object_id
    and name <> 'sysdtslog90'
end

If you are not happy with using the sys.tables view, then you could always define the table list yourself.

Upvotes: 1

Pratap .R
Pratap .R

Reputation: 212

Here is an interesting solution

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Upvotes: 0

Related Questions