Edgar Allan Bayron
  • Member for 5 years, 3 months
  • Last seen this week
How to insert into TABLE Variable?
Accepted answer
11 votes

Use this one: DECLARE @coordinates TABLE ( latitude1 DECIMAL(12,9), longitude1 DECIMAL(12,9) ) INSERT into @coordinates SELECT latitude, longitude FROM loc.locations WHERE ...

View answer
What is the optimized way to pass multiple parameter from a single table to a function?
10 votes

This is the only thing I could think of. You could try this as a basis: DECLARE @p1 DECIMAL(12, 9), @p2 DECIMAL(12, 9), @p3 DECIMAL(12, 9), @p4 DECIMAL(12, 9) ; SELECT @p1 = latitude,...

View answer
How to use a avg expression in the WHERE between clause?
Accepted answer
8 votes

Can you try this instead? Instead of having it in the WHERE clause, use it in HAVING clause. SELECT u.id, AVG(rating_value) avg_rate FROM dt_tutor_comments u GROUP by u.id HAVING AVG(rating_value) ...

View answer
Same function in SELECT and WHERE clause
5 votes

Try to call it again: SELECT * FROM ( SELECT *, f(x, y) AS func FROM table_name ) a WHERE a.func < 10;

View answer
MS SQL server set variable to where clause
3 votes

You can use table variables DECLARE @sqlq TABLE ( adc VARCHAR(100) ) INSERT INTO @sqlq VALUES (21); INSERT INTO @sqlq VALUES (23); SELECT * FROM TBL1 WHERE adc IN (SELECT adc FROM @sqlsq)

View answer
Unique combination of values
3 votes

CREATE UNIQUE INDEX your_unique_index ON your_table(b, c, d); Create an index instead. It has the same as the unique constraint.

View answer
Are there any other ways to get results from Two tables without using Sub Query
2 votes

I need the result of the customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 ...

View answer
SQL Server TRUNCATE TABLE permissions
Accepted answer
2 votes

It is not possible to grant TRUNCATE without granting ALTER to the user. Though you can have a work around by granting DELETE to the user.

View answer
How to join multiple columns from different tables
Accepted answer
2 votes

Try this one, use INNER JOIN from booking to aircraft_model SELECT b.booking_id, c.firstname AS First_Name, c.last_name AS Last_Name, am.aircraft_model FROM booking b INNER JOIN ...

View answer
i am trying to get total amount from this query
2 votes

Error occurred due to a column which is not part of the equation is inside the parenthesis. Try this: SELECT s.emp_id, (s.[basic] + s.da + s.allowance) - ((s.deduction - p....

View answer
How to change the current database in an SQL Query window in SSMS?
2 votes

Read this link This issue occurs because Azure SQL Database does not support the "use" command. You have to install the latest cumulative update for SQL Server

View answer
Select rows from table having two specific values
Accepted answer
2 votes

If what you want is to find ProductID that has both 2 and 6 values, try below query: -- Assuming table is named #tmp SELECT a.* FROM #tmp a INNER JOIN ( SELECT ProductID, ...

View answer
Select distinct id with max date according to department
2 votes

SELECT * FROM ( SELECT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE, RANK() OVER (PARTITION BY CCSMASTERLISTREVNO ORDER BY salary ...

View answer
HOW to work with NULL in a NOT NULL column?
2 votes

No you cant insert NULL in a NON NULL column. That's what constraints are for. If you want, you can alter the column. ALTER TABLE dbo.mytable ALTER COLUMN column_a INT NULL Or just insert 0 for ...

View answer
Output of UNION query
1 votes

Yes, you will have output even if only 1 of the SELECT query has results. Though by default, you cannot determine if the result was from the 1st or 2nd SELECT query, you can include an additional ...

View answer
T-SQL How to Create a new column as INT from Datetime column as YYMM or YYYYMM
1 votes

Try below snippet: DECLARE @datetime AS DATETIME SET @datetime = '2018-05-15 09:45:00.000' SELECT CAST(LEFT(CONVERT(VARCHAR(20), @datetime, 20), 4) + SUBSTRING(CONVERT(VARCHAR(20), @datetime, 20), 6,...

View answer
Select unique values for one column
1 votes

Try this: SELECT a.set_name, b.value FROM table1 a INNER JOIN ( SELECT value, COUNT(1) AS val_count FROM table1 GROUP BY value HAVING COUNT(1) = 1 ) ...

View answer
Pulling SQL Server data into Excel
1 votes

Have your declaration at the top.. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. DECLARE @...

View answer
Which will be faster for Millions of records? Procedure or Insert statement with multiple values?
1 votes

Stored Procedure is better, why? Read this: http://www.sqlbook.com/SQL-Server/Why-use-Stored-Procedures-41.aspx When a query runs, it calculates the most efficient method of processing the query and ...

View answer
Suppress repeating values
1 votes

Here, I have tried and tested it and it gives the output you wanted (this was tested in Oracle): SELECT CASE WHEN dr <> 1 THEN NULL ELSE demand_ind END demand_ind, ...

View answer
Query whith DATETIME
1 votes

Replace CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada, CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida, DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia with CONVERT (VARCHAR(20), ...

View answer
Can we downgrade oracle database form 12cR2 to 12cR1
1 votes

You can downgrade both major releases and patchset releases, based on the original Oracle Database release from which the database was upgraded. Major release downgrades are supported back to Oracle ...

View answer
What is difference between mongodump and mongoexport?
0 votes

Here mongodump is a utility for creating a binary export of the contents of a database. mongodump is used for full backup, partial backup, syncing from production to staging/development, and changing ...

View answer
Linked Server Insert Permission
Accepted answer
0 votes

Sorry, I was in a hurry to ask it since it was urgent. For those who will encounter it, this may help: Check the user that was used in the linked server. That permission in the linked server (in my ...

View answer
How to list all the inherited tables from the parent table?
Accepted answer
0 votes

Try this: SELECT table_name FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema = 'database_name' AND referenced_table_name = 'user'; This will list of referenced tables under table 'user'.

View answer
Contacts table with multiple entries?
0 votes

You may want to add 3 additional tables (2 of them are linking tables), so it will be: Users Users_Contacts (to address multiple users in a single contact) EmailAddress (to address multiple users in ...

View answer
How to restore accidentally truncate mysql table
0 votes

See this link for the answer. Hope this helps. If you have binary logging enabled, you can replay changes that went into creating the stats table, up to the point where you did the TRUNCATE. But you ...

View answer
SQL Server Cluster: There is no SQL Server failover cluster available to join
0 votes

Node 2 needs to have SQL Server installed "with failover cluster" to be seen from node 1 configuration wizard.

View answer
Invalid Column Name Error after ALTER and UPDATE
0 votes

Just do this. Execute first the ALTER statement ALTER TABLE Intraday_Forecast_temp ADD IssueTimeUTC SMALLDATETIME Have it executed successfully then execute the UPDATE statement. UPDATE ...

View answer
Drop article from Replication
0 votes

Try this: Remove a table/article from the subscription, go to Publisher properties. On the Articles tab, uncheck the tables (schema name are indicated there). Click OK then re-run the snapshot ...

View answer