mhoyt
Member
We wish to copy all tables defined by a SQL query from a JDE_CRP to a JDE_DEVELEOPMENT SQL Server 7 database. A script was written to do this using a cursor that passed table owner from (CRPDTA), table owner to (TESTDTA) and table name to a stored procedure.
The stored procedure truncated the table and then used INSERT INTO TESTDTA,tblname select * from CRPDTA.tblname
This works fine but takes about a hundred years to run. It seems to be logging each record inserted even when truncate log om checkpoint is set.
Using DROP TABLE, SELECT INTO ... is much faster but obviously kills indexes, security, etc...
Using a database backup and then restore into new database is faster but we lose views, security, etc ...
We are looking for a way to quickly copy the tables into EXISTING TABLES with EXISTING SECURITY, etc... This should not affect any tables, views, procedures, etc... not specifically named in the script.
Thinking now we may have to use BCP and BULK insert but that is just plain ugly.
Any help would be appreciated.
Thanks
Mike Hoyt
Database Analyst
IMP Group International
The stored procedure truncated the table and then used INSERT INTO TESTDTA,tblname select * from CRPDTA.tblname
This works fine but takes about a hundred years to run. It seems to be logging each record inserted even when truncate log om checkpoint is set.
Using DROP TABLE, SELECT INTO ... is much faster but obviously kills indexes, security, etc...
Using a database backup and then restore into new database is faster but we lose views, security, etc ...
We are looking for a way to quickly copy the tables into EXISTING TABLES with EXISTING SECURITY, etc... This should not affect any tables, views, procedures, etc... not specifically named in the script.
Thinking now we may have to use BCP and BULK insert but that is just plain ugly.
Any help would be appreciated.
Thanks
Mike Hoyt
Database Analyst
IMP Group International