Questions tagged [select-into]

The SQL command SELECT INTO statement copies data from one table into a new table. See https://www.w3schools.com/sql/sql_select_into.asp

SELECT INTO is available in various database systems so please tag the type of database being used as well.

158 questions
2
votes
1 answer

Audit trigger in sql azure

I added a trigger to the table to copy the inserted data to an audit table. I got all the column names of the table from INFORMATION_SCHEMA. I used "SELECT * INTO #INSERTED FROM INSERTED" to copy inserted data to a temporary table. Then used the…
2
votes
1 answer

The default value of column is missing when using 'select into table' to copy table in sql server

As you know, we can use 'select into table' to copy table structure in sql server, but there's a problem when the column of source table has default value, the default value setting is missing in the newly created table. So, how can I keep the…
gfytd
  • 1,747
  • 2
  • 24
  • 47
2
votes
1 answer

select into and overwrite

I have this SQL Server Agent, that creates a backup table, doing this: DECLARE @date datetime, @insert_cmd varchar(1000) SET @date =GETDATE() set @insert_cmd = 'select * into [tablename_'+CONVERT(varchar(100), @date, 112 )+'] from tableA' exec…
russds
  • 845
  • 5
  • 25
  • 48
2
votes
1 answer

select into using union

I need to using both "union" and "into" in a query. These two versions works ok SELECT x.* INTO NewTABLE FROM (SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2) x Or SELECT x.* INTO NewTABLE FROM (SELECT val1, val2 FROM TABLE1 UNION SELECT…
user1571823
  • 394
  • 5
  • 20
1
vote
1 answer

PL/pgSQL: Can we store a query result into a variable

I'm trying to create a function in plpgsql like: CREATE OR REPLACE FUNCTION select_left_photo_ids(in_photo_id bigint[], in_album_id bigint, in_limit int) RETURNS SETOF bigint[] AS $$ DECLARE photo_count int; DECLARE photo_ids bigint[]; BEGIN …
Mayank
  • 5,454
  • 9
  • 37
  • 60
1
vote
2 answers

Is there any significant difference between using SELECT ... FROM ... INTO syntax instead of the standard SELECT ... INTO ... FROM?

I was creating a function following an example from a database class which included the creation of a temporary variable (base_salary) and using a SELECT INTO to calculate its value later. However, I did not realize I used a different order for the…
1
vote
1 answer

Is there a way to consolidate this insert into select query?

Use case: For some particular test qa users stored in the users table, I want to update or create values in another table named user_limits so they have high limits of values 1000 users table definition: create table if not exists users ( id int…
Kristi Jorgji
  • 1,154
  • 1
  • 14
  • 39
1
vote
2 answers

PotgresSQL INSERT INTO SELECT CASE problem

I have the the following query and I'd like to insert any number of rows from the sub queries. Of course I'm getting an error SQL Error [21000]: ERROR: more than one row returned by a sub query used as an expression since some of the sub queries…
1
vote
1 answer

Proc Sql Select Into Is Creating a Temporary Variable that I can't Call

I am trying to use proc sql select into to create a variable that I then try to call later. This variable is the average price (BlockPrice). proc sql; create table Block_Price_Calc as select mean(Price) Into : BlockPrice from Data1 Where…
ldan
  • 41
  • 1
  • 6
1
vote
3 answers

Building a comma-separated list of values in an Oracle SQL statement

I'm trying to build a comma-separated list of values out of a field in Oracle. I find some sample code that does this: DECLARE @List VARCHAR(5000) SELECT @List = COALESCE(@List + ', ' + Display, Display) FROM TestTable Order By Display But when I…
VBAHole
  • 1,508
  • 2
  • 24
  • 38
1
vote
2 answers

select highest correlated pairs from a matrix SAS

I have a dataset like this data have; do i = 1 to 1000; y = ranuni(0); x1 = y ** 2; x2 = x1 ** 3; x3 = x2 - x1/2; output; end; run; I build a correlation matrix like this: proc corr data = have out =…
78282219
  • 159
  • 1
  • 12
1
vote
1 answer

Check if a variable was set from SELECT INTO in MySQL Triggers

I'm not sure if this is already answered or not, but I couldn't find a good solution. I have a trigger for my table (source_table), where it will populate another table (target_table). However, when populating the target_table, I need to get a value…
Praneeth Peiris
  • 2,008
  • 20
  • 40
1
vote
2 answers

Getting a non-NULL-able warning when using SELECT * INTO

When I execute the following proc, I get this warning: Attempting to set a non-NULL-able column's value to NULL. USE [DbTwo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter proc [dbo].[TEST_warning_proc] as IF…
Eliezer
  • 429
  • 1
  • 9
  • 20
1
vote
2 answers

"Syntax error at or near ' , '" while trying to SELECT INTO

The query for selecting multiple values and assigning to multiple variables in a single SELECT query leads to an error. My Postgres version is 9.5. The query is: SELECT INTO region_id ,doc_type,tax_amt…
Arun
  • 65
  • 1
  • 7
1
vote
1 answer

Select Into Variable and use it into IF (oracle)

I got procedure where I want to insert value to variable from Col1: Procedure insertX IS var1 varchar2(100) := ''; check_s1 : = 'select Col1 into '||var1||' from table1'; begin EXECUTE IMMEDIATE check_s1; if var1 is null then ... …
4est
  • 3,010
  • 6
  • 41
  • 63