SQL Select Multi-Columns into Multi-Variable

SQL SELECT multi-columns INTO multi-variable

SELECT @variable1 = col1, @variable2 = col2
FROM table1

SQL Server : selecting multiple columns into multiple variables in a stored procedure

Not sure if I understand your issue. If you want to set multiple variables at once:

DECLARE @myInt INT;
DECLARE @myDate DATETIME;

SELECT @myInt = someInt, @myDate = someDate
FROM someTable
WHERE myName = someName

Also note if the select fetches many rows the variables will hold the last rows values. In fact the variables will be set for each row fetched.

Also note in SQL Server you don't have a parameter to the Int declaration. Maybe you want to use Decimal(20).

Select multiple columns into multiple variables

Your query should be:

SELECT T1.DATE1, T1.DATE2, T1.DATE3
INTO V_DATE1, V_DATE2, V_DATE3
FROM T1
WHERE ID='X';

Setting multiple variables for one column in SELECT statement

In a single select we can assign both the variables using conditional aggregate

Try this way

DECLARE @source NVARCHAR(250)
DECLARE @target NVARCHAR(250)

SELECT @source = Max(CASE WHEN KEY = 'Source' THEN Value END),
       @target = Max(CASE WHEN KEY = 'Target' THEN Value END)
FROM   ApplicationSetting
WHERE  KEY IN( 'Source', 'Target' ) 

SELECT query for multiple columns with multiple variables

So, this isn't a direct answer per se, but rather some guidance on general SQL approach and troubleshooting which may help you get to the root of the issue.

First, its rarely a good idea to run raw SQL statements directly from code. Instead, it's a better idea to set up a stored procedure to execute the specific code you wish to run. The advantage here is that you separate issues using the Python client with issues involving your data query itself. By creating a sp, you'll also be validating the syntax of the command itself.

Second, once you get the stored procedure syntax correct, you can begin troubleshooting on the python code itself. I think you'll find that it is much easier to work with the SQL command object than trying to do it the way you are. This tutorial may be a good starting point.

Third, to ensure duplicates do not get inserted into a database, you create unique indexes. The correct way to achieve the goal here would be to attempt the insert, and the insert would fail if there is a duplicate. Here is a resource which might help with that.

Proper syntax for insert command

insert into supermarket (product, origin, amount, image, best_before_date)
values (@product, @origin, @amount, @image, @best_before_date)

Proper syntax to create index

CREATE UNIQUE INDEX IX_supermarket
  using HASH
ON supermarket (product, origin, amount, image, best_before_date)

MySQL: Selecting multiple fields into multiple variables in a stored procedure

Your syntax isn't quite right: you need to list the fields in order before the INTO, and the corresponding target variables after:

SELECT Id, dateCreated
INTO iId, dCreate
FROM products
WHERE pName = iName

Set multiple variables using one SQL query that returns one row using SELECT TOP

You can use TOP (n) with SELECT statement :

SELECT TOP (1) @var1 = col1, @var2 = col2, ..., @varn = coln 
FROM table 
WHERE col = @col 
ORDER BY id DESC;

how to select multiple column name dynamically In SQL Server

You should only use Dynamic SQL for this purpose.
like this :

declare @columns varchar(max) = 'Col1 , Col2 , Col3' ;    
declare @sql varchar(mx) = 'SELECT '+ @columns +'FROM emp' ;

How do I return values from multiple columns when the column names are based on a variable result

The following query using a dynamic UNPIVOT operation will do the work:

CREATE TABLE #yourTable ( [record id] INT,[current stage] VARCHAR(255), [met client] DATE, [contract agreed] DATE, [service completed] DATE, [on hold] DATE)

INSERT INTO #yourTable VALUES 
(11111, 'met client', '2019-01-02', NULL, NULL, NULL),
(22222, 'contract agreed', '2019-01-02', '2019-01-20', NULL, NULL),
(33333, 'on hold', '2019-01-02', '2019-01-20', NULL, '2019-02-10'),
(44444, 'service completed', '2019-01-02', '2019-01-20', '2019-03-01', '2019-02-10')

DECLARE @col NVARCHAR(MAX) = '';
SELECT @col += ',' + QUOTENAME([current stage]) FROM #yourTable
SET @col = STUFF(@col,1,1,'')

EXEC ( 'SELECT unpiv.[record id], unpiv.[current stage], [Date] AS [Date_of_current_stage] FROM #yourTable UNPIVOT ([Date] FOR [Stage] IN ('+@col+') ) unpiv WHERE [current stage] = [Stage]')

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

---

Related Topics