check a table before truncate another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

check a table before truncate another table

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-14 : 05:15:06

I added a job to get new data every day using statement below:

truncate table A
insert into A select * from B

How to check table B condition before truncate A?
For example, if B is not available or there is no data in it, stop truncate.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-14 : 05:53:16

There will be 2 conditions


If (Exists (Select * From Information_Schema.Tables where Table_Name = 'B'))  And (Select Count(1) From B) >0
   Truncate Table 
else 
  --Do something differnt.

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-14 : 07:09:06

Why do you want to have two tables with same data?

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-14 : 07:36:40

I tested it, it works great!
Can I only use "Select Count(1) From B) >0"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-14 : 07:41:48

The first check, is too check that whether the table exists in the database. if you are 100% sure
that table is there in the database then you remove that check only you use the second condition

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2006-10-14 : 07:59:16

I think you may need to nest these - in case SQL does the COUNT(*) before the TABLE EXISTS

I know it should be left to right, but I've had trouble with

IF IsDate(@MyDate) AND CONVERT(datetime, @MyDate) > @MyStartDate

in the past ... can't remember exactly what the issues was ... but the solution would be:


If (Exists (Select * From Information_Schema.Tables where Table_Name = 'B'))
BEGIN
    IF (Select Count(1) From B) >0
    BEGIN
        Truncate Table 
    END
END
else 
  --Do something different

Kristen

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-14 : 08:03:08

I tested and found that if only use select count statement, in case of there is no table B, query will got error and stop.
I have 20 tables need to truncate and insert, how to make job keep on going even one of tables not existing only using count statement?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-14 : 08:13:55

or you can make use of this query


if (
SELECT i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 and o.[name] = 'TableName') > 0 
  --Trucate 
else 
  - Dont Truncate.

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-14 : 08:17:10

just before using that query, make sure you read post, posted by Kristen in this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35565&SearchTerms=sysindexes

Chirag

http://chirikworld.blogspot.com/

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-10-14 : 08:34:13

Another question:

If table B is in remote server, this statement did not work.
How to make it work? If linked server already being created.

Kristen
Test

22859 Posts

Posted - 2006-10-14 : 08:58:39

"only using count statement?"

Why only use a COUNT statement? What problem do you have combining that with an EXISTS for the table itself?

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-14 : 09:07:27

[code]
if (
SELECT i.rowcnt
FROM SERVERNAME.DATABASENAME.DBO.sysobjects o
LEFT OUTER JOIN SERVERNAME.DATABASENAME.DBO.sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 and o.[name] = 'TableName') > 0
--Trucate
else
- Dont Truncate.
[/code]

Chirag

http://chirikworld.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-15 : 00:42:03

You can check if the table exists with a function call.


-- Check if table B exists
if  objectproperty(object_id('dbo.B'),'IsUserTable') = 1
	begin
	-- Check if there are any rows in table B
	if (select top 1 1 from dbo.B ) > 0
		begin
		truncate table dbo.A
		insert into  dbo.A select * from dbo.B
		end
	end

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-10-15 : 13:57:07

"if (select top 1 1 from dbo.B ) > 0"

That's an interesting idea

I wonder how it fares compared to

IF EXISTS (SELECT * FROM dbo.B )

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources