New answers tagged t-sql
6
votes
Accepted
Is this valid SQL Server Update syntax?
The syntax:
...
SET (shipcountry, shipregion, shipcity) =
( SELECT country, region, city
...
)
...
is valid SQL syntax but not valid SQL Server syntax. It has not yet been implemented ...
5
votes
Accepted
sys.dm_exec_describe_first_result_set returns wrong data type
System types 106 and 108 are decimal and numeric, respectively. These type names are synonyms and can be used interchangeably:
SELECT system_type_id, name
FROM sys.types
WHERE system_type_id IN(106, ...
- 24.7k
-1
votes
Insert rows into a table from another table row?
Unpivot data
SELECT userid, weekday, status
FROM (SELECT userid, Sunday, Monday, Tuesday, Wednesday,Thursday,Friday, Saturday
FROM schedule) scd
UNPIVOT
(Status FOR weekday IN (Sunday, ...
- 19
2
votes
How does this piece of TSQL work?
The workaround I prefer is creating the temp table first, then doing an INSERT INTO in your branched code like so:
DROP TABLE IF EXISTS #SomeTable;
CREATE TABLE #SomeTable (Field1 INT, Field2 VARCHAR(...
- 26.6k
4
votes
Accepted
How does this piece of TSQL work?
The error is generated at parse time, so before the batch is even executed. And it only happens when the INTO target is a temporary table (either local or global). It does not occur when the target ...
- 21.6k
3
votes
How does this piece of TSQL work?
Why the following batch doesn't work as expected?
There are some guru's here that can probably explain this much better, but what happens in your first variant is that the table is declared twice. ...
- 4,900
2
votes
Accepted
How to combine multiple rows into one, based on event id, datetime, and an action into their own columns?
You can use pivot or a combination of MAX + CASE
CREATE TABLE Table1
([eventid] varchar(36), [eventtime] datetime, [action] varchar(7))
;
INSERT INTO Table1
([eventid], [eventtime], [...
- 3,866
3
votes
Accepted
Converting rows to desired JSON format in TSQL
That shape doesn't come easy to FOR JSON to transform data (column values) into metadata (json keys). But it's easy to do with string_agg, eg:
select t.id,
'{' + STRING_AGG(concat(quotename([key]...
- 41.7k
1
vote
sql server agent job with xp_cmdshell to move files
Its because C:\SomeFiles\FileArchive\ is a Sub-Directory of C:\SomeFiles. Try running the command from a command prompt to test.
move /Y C:\SomeFiles\*.* C:\SomeFiles\FileArchive\
Will get you what ...
- 11
0
votes
variable grouping? or filling available slot from pool
With large amounts of data, I would not do this as a single CTE, but this should give you point to iterate from:
WITH Groupings AS (
SELECT 10 AS SIZE
)
, SequenceOfNumbers AS (
SELECT ...
- 589
0
votes
SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'
In my case, I was logged into SSMS with Windows Authentication... reading your comments I realized that. After loged with the sa user, it worked fine. Thanks!
0
votes
How to enhance performance when compare two table feature value to run faster?
First, I would make RecomendationId the clustered primary key on both tables:
ALTER TABLE [dbo].[TechnologyOriginalFeaturesEqual]
ADD CONSTRAINT PK_TechnologyOriginalFeaturesEqual_RecomendationId ...
- 589
0
votes
Adding a new column to a table that has more than 1 billion rows
Adding a nullable column should be fairly quick. SQL Server does not actually allocate that space on the disk to existing records until there is an actual value that it needs to store. Keep in mind ...
- 589
2
votes
CASE clause doesn't work on column sql server
I actually figured it out
the thing is, It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators ...
- 31
5
votes
Doesn't T-SQL support correlated CROSS JOINs?
To answer the main question:
Doesn't T-SQL support correlated CROSS JOINs?
No. CROSS joins are not correlated in SQL (any variant, not only T-SQL).
Is there a workaround?
Yes. Correlated joins are ...
0
votes
Generate a script to drop a DB user
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250))
SET @DBuser_sql =
'SELECT ''?'' AS DBName,a.name AS Name FROM ?.sys.database_principals a
...
- 3,655
Top 50 recent answers are included