SQL - Check if a table is empty before inserting rows

ttist25
ttist25 used Ask the Experts™
on
Hello there!

I'm using SQL Server 2012 and I'm trying to figure out a simple way to check if a lookup table has any rows in it and, if it doesn't have rows insert values into it, else don't insert rows.

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
declare @n int
select @n =  count(*) from yourTable
if @n > 0
 insert into yourTable erc...
Distinguished Expert 2017

Commented:
You can select count() on the table.

Depending on the table use/purpose
If table purpose is temporary, checking for the resistance of table from schema, then checking for count

Another create the table if missing, populate with info. Do what is needed. Delete table.
If not creating. Do not run.
ste5anSenior Developer

Commented:
To avoid concurrency issues:

USE tempdb;
GO

DROP TABLE IF EXISTS #Sample;
GO

CREATE TABLE #Sample (
    ID INT NOT NULL ,
    Payload INT NOT NULL
);
GO
;

WITH DataToInsert ( ID, Payload )
AS ( SELECT 1 ,
            1 )
INSERT INTO #Sample WITH ( TABLOCKX ) ( ID ,
                                        Payload )
            SELECT D.ID ,
                   D.Payload
            FROM   DataToInsert D
            WHERE  (   SELECT COUNT(*)
                       FROM   #Sample S ) = 0;
GO

SELECT *
FROM   #Sample S;
GO

DROP TABLE IF EXISTS #Sample;
GO

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

great code sample ste5an !
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Use EXISTS() not COUNT.  There's no reason to waste time counting every row in the table when you only need to know if one row exists.

INSERT INTO dbo.tablename
SELECT ...
FROM ...
WHERE NOT EXISTS(SELECT TOP (1) * FROM dbo.tablename)
look into the "merge ... when matched " syntax. This allows to spevify custom actions depending on existing rows with the same primary key.

If you just want to skop the insert when there is an expecting row , you can also ignore the adequate duplicate key  error code in the client app.

Author

Commented:
Thanks for the answers guys.  I took a little bit from a few and ended up with this:

DECLARE @n INT
SELECT @n =(SELECT COUNT(1) FROM MyTable)
IF @n = 0
BEGIN
INSERT INTO
MyTable(
            Field1,
            Field2,
            Field3,
            Field4
      )
VALUES
 ('1','2','3','4')
END
ELSE
BEGIN
      PRINT 'MyTable has already populated'
END

Thanks again!
glad to be of help
Partha MandayamTechnical Director

Commented:
if not exists( select * from tbl1)
insert into tbl1 select & from tbl2
ste5anSenior Developer

Commented:
As I said, using IF has concurrency issues. E.g.

1)

WAITFOR DELAY '00:00:03';

DECLARE @n INT = (   SELECT COUNT(*)
                     FROM   ##Sample );

IF @n = 0
BEGIN
    INSERT INTO ##Sample
    VALUES ( 1, 2 );
END
ELSE
BEGIN
    PRINT '#Sample has already populated';
END;

Open in new window

2)

DECLARE @n INT = (   SELECT COUNT(*)
                     FROM   ##Sample );

WAITFOR DELAY '00:00:05';

IF @n = 0
BEGIN
    INSERT INTO ##Sample
    VALUES ( 1, 2 );
END
ELSE
BEGIN
    PRINT '#Sample has already populated';
END;

Open in new window

Start 1) first, then run 2) in a second window..

This can be avoided as already wrote:

1)

WAITFOR DELAY '00:00:03';

WITH DataToInsert ( ID, Payload )
AS ( SELECT 1 ,
            1 )
INSERT INTO ##Sample WITH ( TABLOCKX ) ( ID ,
                                         Payload )
            SELECT D.ID ,
                   D.Payload
            FROM   DataToInsert D
            WHERE  (   SELECT COUNT(*)
                       FROM   ##Sample S ) = 0;

SELECT CAST(@@ROWCOUNT AS NVARCHAR(255)) + ' rows inserted.';

Open in new window

2)

WAITFOR DELAY '00:00:05';

WITH DataToInsert ( ID, Payload )
AS ( SELECT 1 ,
            1 )
INSERT INTO ##Sample WITH ( TABLOCKX ) ( ID ,
                                         Payload )
            SELECT D.ID ,
                   D.Payload
            FROM   DataToInsert D
            WHERE  (   SELECT COUNT(*)
                       FROM   ##Sample S ) = 0;

SELECT CAST(@@ROWCOUNT AS NVARCHAR(255)) + ' rows inserted.';

Open in new window

Start 1) first, then run 2) in a second window..
The issue is not with the if. Rather a combination of not locking anything between queries and the default transaction isolation level in mssql which guarantees consistency within transactions but not across them.

Use a select for update in your first query and it should work. or explicitely lock the table.

both have performance impact and there should be a MUCH better way to do that either in the app or by using an atomic query such as the if not exists ... @parta suggested above.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial