SQL - Check if a table is empty before inserting rows

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.

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

Select allOpen in new window

great code sample ste5an !

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.

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

if not exists( select * from tbl1)
insert into tbl1 select & from tbl2

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;

Select allOpen 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;

Select allOpen 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.';

Select allOpen 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.';

Select allOpen 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.