Quickest way to clear all tables
-
What is the easiest way to clear all tables for data in SQL Server 2005 (or express 2005)?
I have had a test database running for some time, and would like to find the fastest way to clear all data in the database before importing real data from access.
Secondly - what is the easiest way to import the data from access to sql server 2005 (both standard and express).
Thanks in advance.
-
To clear the data from all tables in a database I have used this script many times successfully. Just be careful.
if exists (select 1 from tempdb.dbo.sysobjects where name like '%work' and type = 'u')
begin
drop table ##work
end
declare @SQL nvarchar(1000),
@Count int,
@Loop int,
@Name nvarchar(100)
select name,0 process into ##work from sysobjects where type = 'u' order by name
set @Count = (select count(*) from ##work where process = 0)
set @Loop = 1
while @Loop <= @Count
begin
set @Name = (select top 1 name from ##work where process = 0)
--print @Name
set @SQL = 'truncate table '+@Name
exec (@SQL)
set @Loop = @Loop + 1
update ##work
set process = 1
where name = @Name
end
select * From ##work
drop table ##work
To import data from Access just right click on the database name in SQL 2005 and select TASKS. From the next menu select IMPORT DATA. This will open a wizard that will walk you through the process. If you have any problems or questions simply click on HELP. This opens up Books on Line to the subject you are working with. BOL is a DBAs best friend.
Good Luck
-
That'll fail if you have foreign key constraints. You'd need to use delete statements on those tables. Also you'll have to delete from the child tables first.
I already saw a script use sysdepends to solve this problem but I don't remember who posted it and when it was posted (in the last 6 months).
-
I'm working on reinventing the wheel on this one... but using sysreferences instead of sysdepends. Will post the script when I get it finished.
By the way, jmcgarvey's script will also fail if any of the tables are involved in replication.
John
-
Might be wrong on the system table used on that script. Let's just say I didn't try to memorise it
. I usually just use a build script for the whole DB. Or use the old backup / restore method. -
the quickest way to clear tables is to truncate them, drop does not clear it removes - slight difference!!
if you're going to drop all the tables then you might just as well drop the entire database.
dropping tables is usually quicker by truncating then dropping - assumes a reasonable amount of data.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Who said anything about dropping a table??
Good point tho!
-
Yes. Whether it's a DELETE FROM, DROP or TRUNCATE, it's not going to work if the table is referenced by a foreign key constraint.
Come to think of it, writing a script to truncate tables in a database according to their foreign key hierarchy is an interesting academic exercise, but it would be easier, I think just to drop the foreign keys and recreate them afterwards. I think I've posted the srcipt below before. It's actually written for SQL Server 2000, but assuming the sp_fkeys and sp_MSforeachtable stored procedures still exist in SQL Server 2005, it should still work.
Run the script below to generate the DDL to create the FKs, then write your own script to remove them all (that part's easy). Then you can use jmcgarvey's script to truncate the tables and run the results of my script to recreate the constraints. Simple! As usual, use this at your own risk and run it in a test environment first.
I'll post the "hierarchical" script if I get a chance to finish it.
John
set nocount on
--Create temp table to hold FK information.
--May need to increase varchar sizes if you have composite
--keys with many columns or long names
create table #FKs (PKTABLE_QUALIFIER sysname null, PKTABLE_OWNER sysname,
PKTABLE_NAME sysname, PKCOLUMN_NAME varchar(256), FKTABLE_QUALIFIER sysname null,
FKTABLE_OWNER sysname, FKTABLE_NAME sysname, FKCOLUMN_NAME varchar(256),
KEY_SEQ smallint, UPDATE_RULE smallint, DELETE_RULE smallint,
FK_NAME sysname null, PK_NAME sysname null, DEFERRABILITY smallint)
--Populate the table.
--The STUFF function in here gets rid of the [dbo]. from the front of the table name
EXEC sp_MSforeachtable 'DECLARE @table sysname
SET @table=CAST(STUFF(''?'', 1, 6, '''') AS sysname)
INSERT INTO #FKs EXEC (''sp_fkeys '' + @table)'
--Create temp table to contain cascade actions
create table #Cascade (Number tinyint, CascadeAction varchar(9))
--Populate the table
insert into #Cascade
select 0, 'CASCADE' union
select 1, 'NO ACTION'
--Concatenate cols of composite keys into one row
declare @maxseq smallint
declare @i smallint
set @i = 1
select @maxseq = max(KEY_SEQ) from #FKs
while @i < @maxseq
begin
update f1
set f1.PKCOLUMN_NAME = f1.PKCOLUMN_NAME + ', ' + f2.PKCOLUMN_NAME,
f1.FKCOLUMN_NAME = f1.FKCOLUMN_NAME + ', ' + f2.FKCOLUMN_NAME
from #FKs f1 join #FKs f2
on f1.KEY_SEQ = f2.KEY_SEQ - @i
and f1.FK_NAME = f2.FK_NAME
and f1.KEY_SEQ = 1
set @i = @i + 1
end
--Generate the script
SELECT 'ALTER TABLE ' + f.FKTABLE_NAME + '
ADD CONSTRAINT ' + f.FK_NAME + ' FOREIGN KEY (
' + f.FKCOLUMN_NAME + '
 
REFERENCES ' + f.PKTABLE_NAME + ' (' + f.PKCOLUMN_NAME + '
 
ON DELETE ' + cd.CascadeAction + '
ON UPDATE ' + cu.CascadeAction + '
'
from #FKs f
join #Cascade cu
on f.UPDATE_RULE = cu.Number
join #Cascade cd
on f.DELETE_RULE = cd.Number
WHERE f.KEY_SEQ = 1
-
The migration assistant is fairly handy as well.
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
-
TRUNCATE would be fine if there are no foreign keys, and I doubt you'd be contemplating this action on a replicated database. If you have foreign keys your choices are DROP or DELETE, and unless the amount of data is trivial you probably don't want to use DELETE.
The Generate Scripts task in Management Studio can generate a DROP script and a CREATE script for all the tables. You can set the options to include indexes, constraints, permissions, etc. If none of the tables are referred to by a foreign key from an unselected table, it "should" be able to figure out the dependencies so the DROP TABLE commands are in the proper order.
-
If the Generate Scripts task is not smart enough to drop tables in the correct order, this script will do it. You could add an EXEC to fully automate dropping all the tables, but you're on your own there. I like to see the resulting commands before I run them to avoid unnecessary excitement.
If you want something more robust, with "IF EXISTS" all over the place, you could use SMO scripting to write your own task.
DECLARE
@n int
SET @n = 1
CREATE TABLE #tables (
name sysname NOT NULL,
object_id int NOT NULL PRIMARY KEY clustered,
level int NOT NULL)
INSERT INTO #tables
SELECT name, object_id, 1 AS level FROM sys.tables
-- WHERE name IN (...)
WHILE @@ROWCOUNT > 0 BEGIN
SET @n = @n + 1
UPDATE a SET level = @n
FROM #tables a
INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = a.object_id
INNER JOIN #tables b ON b.object_id = fk.referenced_object_id
WHERE b.level = @n - 1
END
SELECT 'DROP TABLE ' + name FROM #tables
ORDER BY level DESC
DROP TABLE #tables
-
when it gets that complex it might be easier to drop the database .. however you can usually place the truncate / drop code in a loop which doesn't exit on error and after a few iterations it will have the desired effect - no need to worry about FK's.
Yup a replicated database will give problems but so will schema bound views - I figure the original poster didn't have this issue.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
this might help: it created either the appropriate DELETE or TRUNCATE statement, and created them in Foreign key hierarchy order.
you'd want to enahcne this so that it doesn't delete your lookup tables (ie status, state, city, county, whatever)
nocount on
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName from #tables where level = 0
select 'DELETE ' + TableName from #tables where level > 0 order by level
drop table #tables
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply