Delete All from Table

delete all from table

You can use the below query to remove all the rows from the table, also you should keep it in mind that it will reset the Identity too.

TRUNCATE TABLE table_name

Delete from two tables in one query

Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

OR

Just use INNER JOIN as below

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

For more information, check here.

Delete all records in the table that are less than the last year's current date, but don't delete the end dates for the given months for past 2 years

This will do it:

DELETE FROM tablename
WHERE DateStarted < ADD_MONTHS(sysdate, -12) 
AND DateStarted <> LAST_DAY(ADD_MONTHS(sysdate, -24))

The condition to not delete the end date of the current month of the last year is covered by the 1st condition of the WHERE clause.

See the demo.

Results (remaining rows):

> EMP    | DATESTARTED
> :----- | :----------
> John   | 01-SEP-20  
> Jane   | 29-SEP-19  
> Lauren | 30-SEP-19  
> Caleb  | 30-SEP-18  

How to delete all rows in a table using Office Scripts

UPDATE:

We now have getRowCount API on the table that you can use to solve this scenario. It'll return the actual rows (not counting the header or expansion row).

    // Assuming there's a table in the workbook named Table1
    let rowCount = workbook.getTable('Table1').getRowCount(); // Table1
    // Assuming there's a table in the workbook
    let rowCount = workbook.getTables()[0].getRowCount(); // First table

====

OLD ANSWER

I think we are lacking an API that will provide row count. We'll add that to the backlog.
A workaround is this -

function main(workbook: ExcelScript.Workbook) {
  let table = workbook.getTable("Table26");
  let rowCount = table.getRangeBetweenHeaderAndTotal().getRowCount();
  try { 
    table.deleteRowsAt(0, rowCount);
  } catch (e) {
    if (rowCount === 1 && e.code === 'InvalidArgument') {
       console.log("This error means there's no row to delete.")
    }
  }
}

If row count is 1 and the error code is a specific one that's returned when we delete the 'insert-row', then we can ignore the error.
Again, a better approach would be to use an API on the table object to get the row count and delete rows only when row count >= 1. We'll investigate further to improve the experience.

T-SQL :: TRUNCATE or DELETE all tables in schema

You simply need to wrap your "delete from all the tables" script with a "drop all foreign keys" script at the beginning, and "re-create all foreign keys" script at the end. I show one way to do that here:

• Drop and Re-Create All Foreign Key Constraints in SQL Server

However, I would argue that it is much cleaner to just script out the database and empty objects from source control than spend all this time and effort deleting data from one table at a time.

Anyway an attempt at what you're doing (if you truncate you don't also need to checkident, I'm not sure I would ever use undocumented and unsupported procedures like sp_msforeachtable, and I also stay the heck away from INFORMATION_SCHEMA). Please try this on a test database.

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script   nvarchar(max),
  create_script nvarchar(max)
);
  
DECLARE @drop   nvarchar(max) = N'',
        @create nvarchar(max) = N'';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

INSERT #x(drop_script) SELECT @drop;

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

UPDATE #x SET create_script = @create;

PRINT @drop;
PRINT @create;

EXEC sys.sp_executesql @drop
-- clear out data etc. here

DECLARE @truncate nvarchar(max) = N'';
SELECT @truncate += N'TRUNCATE TABLE ' + QUOTENAME(s.name)
  + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s 
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id];

EXEC sys.sp_executesql @truncate;
EXEC sys.sp_executesql @create;

Notes:

• this is untested. As ludly as I can: try this on a test database.
• this was meant to execute exactly once, so I don't drop the #temp table (it may be useful to keep it alive long enough to troubleshoot if things go south)
• PRINT is not necessarily going to show you the full command that is going to be executed, so it's not a valid way to determine if the script is correct. It is just meant as a quick eyeball. If you really want to view the whole command, you'll need something a little more elaborate.
• this doesn't handle indexed views, and I'm sure there are other limitations that might prevent you from truncating some tables (I'm thinking temporal or always encrypted with enclaves or in-mem), but I would resolve those separately and keep truncate around instead of "fixing" that by using a much more log-intensive delete.

---

Related Topics