New answers tagged

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, ...
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. ...
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]...
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 ...
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