I have multiple temporary tables stored in the

schema for an SSIS pipeline that runs daily. The pipeline extracts data from multiple tables and stores it in the temp tables, which the data of the temp tables is later used to store in a different database. My question is about the performance of the creation and deletion of the temp table, I want to know which approach is more optimal. Which of the following approaches is the better option? And what are the disadvantages and advantages of each one? 1-

temp table if it doesn't already exist -->

temp table from previous data before inserting new data -->

the new data into temp table. 2-

temp table if it exists --> use the statement

INTO

to insert data directly without creating a temp table in a separate statement. edit: the tables in the tempdb are created like this

TABLE tempdb..Table1

We have a SQL database that is a secondary database or should I say a secondary replica in an AlwaysOn Availabiltity Group. As a result, the database is always a read-only database and therefore I can't execute the stored procedure below because the stored procedure attempts to create a new table .. basically I get hte error message: Failed to update database xxxxxx because the database is read-only. It was suggested that a workaround would be to modify the procedure to SELECT ... INTO a table on a different database (that's not read-only / not part of an AlwaysOn Availability Group) that lives in the same server. E.g: INTO SomeOtherDatabase.' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ' Can someone please take a look at my code and help modify the code. I tried the following: INTO tempdb.dbo.@DeltaTable' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ' The above didn't work. The full procedure is as follows: CREATE PROCEDURE dbo.GenerateDeltaTable @Domain VARCHAR(100), @TableName VARCHAR(100), @DeltaTable VARCHAR(100) AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); -- Construct dynamic SQL for dropping and creating the target table SET @sql = ' IF OBJECT_ID(''' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ''', ''U'') IS NOT NULL DROP TABLE ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + '; SELECT T.*, LOWER(CONVERT(VARCHAR(64), HASHBYTES(''SHA2_256'', (SELECT T.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)), 2)) AS signature INTO ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ' FROM ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@TableName) + ' AS T;'; -- Execute the constructed SQL EXEC sp_executesql @sql; END; Any thoughts on how to create the table from the stored procedure to another database

I'm starting on PL/pgSQL, and have written this code to update and return a query, but it isn´t working. This is the SQL error: > SQL Error : ERROR: query has no destination for result data > > Hint: If you want to discard the results of a SELECT, use PERFORM instead. This is my code: CREATE OR REPLACE FUNCTION setKeys(qty int,game_id varchar,nro_order varchar) RETURNS TABLE (id int4, key varchar ) AS $$ DECLARE row_to_update record; contador int; BEGIN contador := 0; LOOP -- Select the single row to update based on a condition SELECT sk.id INTO row_to_update FROM serial_keys sk WHERE sk.id_game = game_id AND sk.state = 'Disponivel' AND sk.order_number IS NULL LIMIT 1; IF row_to_update IS NULL THEN RAISE EXCEPTION 'No matching rows found.'; END IF; select count(sks.id) as flag from serial_keys sks where sks.order_number = nro_order; if flag = 0 then UPDATE serial_keys SET order_number = nro_order, state = 'enviado' WHERE id = row_to_update.id; end if; contador := contador + 1; EXIT WHEN contador = qty; END LOOP; RETURN QUERY SELECT s.id,s."key" FROM serial_keys s WHERE s.id = row_to_update.id; END; $$ LANGUAGE plpgsql; What am I doing wrong?

I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do). Whereas in Postgres, SELECT INTO creates a new table from the results of a query. I want to apply/implement such a thing in Postgres. What is the appropriate command or statement there? Here is my Oracle query: SELECT COUNT(1) INTO ada FROM atk_history_qty WHERE tgl BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd') AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

I have 5 tables in my database with respectively a size of 70Gb, 500Mb, 400 Mb, 110Mb and 20 Mb. I want to create a new table that contains all columns of all tables, so I tried 2 queries, the first one is :

select into new_table as select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

and the second one is :

insert into new_table select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

Before executing these two queries on my big data tables, I tried both on a total 1G database, the first on took only 7s and the second one approximately 10 mn. Now, executing the first one on my huge database, made my disk full even though I had 250Gb free space before running the query, and without finishing the query so I got the follow error :

ERROR:  could not write to temporary file: No space left on device

The second one, is taking a lot of time and consuming my free disk space slowly and, as the first one, not returning the result. What are the difference between these two queries ? Is there a way to make the insert into non transactional so as I can follow my insert steps. And I guess Postgres uses logs (journalization) so is there a way to deactivate that in order to speed up the insertion ? or I should follow another method in order to get a desired result without filling up all disk. Ps : No triggers, only a primary key on each table.

SQL Server 2019 CU15, Database Audit: When auditing a SELECT on a database, everything looks fine, but if I SELECT INTO a #myTempDB, I get double registration in the AUDIT LOG. I created a test database for this purpose, and configured database auditing to monitor SELECT on my test database. USE [master] GO Drop database if exists [AuditTestDatabase]; Create Database [AuditTestDatabase]; Use [AuditTestDatabase] go Create Table AuditAuditTest (N integer) insert into AuditAuditTest (N) Values(1) Select * From AuditAuditTest Create Audit output specification (server audit) (Create the Audit in your Temp) Use [master] If exists (SELECT 1 FROM sys.server_audits where name='AuditFiles') Begin ALTER SERVER AUDIT [AuditFiles] WITH (STATE = OFF); DROP SERVER AUDIT [AuditFiles]; End Go CREATE SERVER AUDIT [AuditFiles] TO FILE ( FILEPATH = N'C:\Temp\Audit\' ,MAXSIZE = 10 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'b630ef61-59c4-4318-83db-eee587c89fbe') ALTER SERVER AUDIT [AuditFiles] WITH (STATE = ON) GO Create Database Audit USE [AuditTestDatabase] GO If exists (SELECT 1 FROM sys.database_audit_specifications where name='DatabaseAuditSpecification') Begin ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification] WITH (STATE = OFF); DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]; End Go CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification] FOR SERVER AUDIT [AuditFiles] ADD (SELECT ON DATABASE::[AuditTestDatabase] BY [public]) WITH (STATE = ON) GO Auditing is now running. This can be verified by opening the Audit folder, where there should be one, and only one, sqlaudit file. **Viewing Audit Logs from SSMS** At this point, it only has information that auditing has started. Next, I try this Select * From [AuditTestDatabase].dbo.AuditAuditTest As expected, the SELECT is now in the audit log Next, I try Select * Into #L From [AuditTestDatabase].dbo.AuditAuditTest Drop Table #L And take another look at Audit log. The SELECT is now present 3 times, this happens every time I select into a temp table. Is this a bug, or is there something I don’t see?

I tried:

Select * Into  From  Where 1 = 2

but the default value constraint is not created. I have a smalldatetime column whose default value is getdate().

I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've narrowed down the problem to execution plan generation. One thing we've noticed that has vexed us is that this query takes a few seconds to run (and display all the data, ~44k rows): SELECT * FROM vw_example whereas this query takes minutes to run (tbl_example is created by the query): SELECT * INTO tbl_example FROM vw_example We've additionally compared the SQL execution plans between the two in Microsoft SQL Server Management Studio (2014). The former plan took 3 seconds to generate. The latter plan took 37 minutes. Comparing the two plans, the only difference is that the latter plan has a "Table Insert" node at the start. Everything else is identical. Does anyone have any idea why it would take significantly longer to generate a plan for the SELECT INTO statement vs a SELECT statement?

I am using SQL Server 2016 and I tried to the following query. SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp This query is obviously in error. Because it does not convert. However, the temporary table (#tmp) is created even if the query fails. Why? I think this is by design, but I want to know why. PDW (parallel datawarehouse) does not create temporary table.

I have a table which is predominantly filled with NULLS and takes up 10 GB which was shocking since probably 90 to 95% of the table is NULLS. I copied this table over to a new database on the same SQL 2012 instance using Select * Into.... but the copied table takes up about 70 MB. Checking them against each other, they seem to have the same data, as expected so it's not like data is missing. Is the disparity in size a result of not generating a script to copy indexes, triggers, keys, etc? Is there a way to optimize the table to reduce space? Thank you,

To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation. The table size was 220 GB and the SELECT .. INTO was a simple one (i.e. SELECT * INTO BACKUP_TABLE FROM ORIGINAL_TABLE) This was a test environment and based on that, I need to work out an estimation of the execution time for the same operation in the production environment. The production environment has 40 CPUs and 64 of RAM. CPUs are identical and I/O systems for both systems are the same. (i.e. disk type and disk layouts are the same). Is it realistic to estimate the production's SELECT .. INTO .. will be complete in less than an hour, considering the production server has 10 times more processing power? If not possible to answer this question based on the above, shall I re-run the test and collect some metrics? If yes, what those metrics should be? Thanks in advance, for providing your thoughts on this!

I'm trying to batch-copy data in a specific order from one table to another in PostgreSQL 12-beta2. The table is *not* using sequences, but contains an composite unique Primary Key (user_id, object_id). In order to determine where to start for the next batch, I'd like to start off from the last inserted row (WHERE user_id >= last_user_id AND object_id > last_object_id). Starting off with this:

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... returns a table of 1000 tuples. I'd like to obtain the last inserted tuple from it. I've tried to do a SELECT around it, like this:

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...) 
    FROM source_table 
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

But that returns a syntax error: ERROR: syntax error at or near "INTO" LINE 2: INSERT INTO dest_table ^ I've also attempted RETURNING ... INTO variable [as described here](https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id/2944481#comment64237933_2944335) , but that fails too: ERROR: syntax error at or near "INTO" LINE 23: RETURNING user_id, object_id INTO my_variable; ^ Do I need to create a function for this (e.g. plpgsql) or am I missing something obvious in plain SQL that let me do this? That would be highly favorable.

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself. The facts: - Usual database size is about 55 GB, it grew to 605 GB. - Log file has normal size, datafile is huge. - Datafile has 85% available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated). - Tempdb size is normal. I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened: - SELECT INTO is executed - Target table is created - Data is inserted as it is selected - Disk fills up, causing the insert to fail - SELECT INTO is aborted and rolled back - Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space. In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this: BEGIN TRANSACTION SELECT T.x INTO TMP.test FROM (VALUES(1))T(x) ROLLBACK SELECT * FROM TMP.test This results in: (1 row affected) Msg 208, Level 16, State 1, Line 8 Invalid object name 'TMP.test'. Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table? Are the assumptions I sketched here correct? Is this a likely scenario to have happened?

Is it possible to use "select into" statement when we are joining different tables which have 1 or more same column names? Eg: Table1 has column name as description and Table2 has column name as description and I want both these columns data in my new table