Questions about the SELECT INTO statement used to create a table from a SELECT statement (or store scalar results into variables in a procedural language). See also the tag [ctas].
Questions tagged [select-into]
10 questions
18
votes
2 answers
Disk space full during insert, what happens?
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…
HoneyBadger
- 357
- 4
- 10
3
votes
0 answers
Double registration when using select into
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…
Torben iisager
- 31
- 2
3
votes
0 answers
Time to generate execution plan for select into from a view takes significantly more time than select
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…
Simon
- 131
- 1
1
vote
1 answer
Select into query, insert fails, but the table is created
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…
Suseong Park
- 11
- 1
1
vote
2 answers
How much storage space do indexes and keys take for an SQL 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…
Sam
- 21
- 1
1
vote
1 answer
SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement
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 ..…
RGO
- 403
- 4
- 11
0
votes
1 answer
Create an empty table from an existing table keeping the default value constraint
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().
Rick
- 113
- 6
0
votes
2 answers
How to speed up an insertion from a huge table with postgres?
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…
Islacine
- 27
- 5
0
votes
2 answers
How can I obtain the last inserted row with INSERT ... SELECT in PostgreSQL?
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…
gertvdijk
- 197
- 2
- 12
-2
votes
1 answer
Select into statement
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…
