New answers tagged union
The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this: SELECT PubKey, Title FROM Publication WHERE EXISTS ( SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey ) UNION SELECT NoteKey, Title FROM Note However, if you want to specify the ...
This will return all the lines of the two tables. You can change the aliases so that you get the column name you want. SELECT t1.col1 as name, t1.col2, t1.col3 FROM t1 UNION ALL SELECT t2.cola as name, t2.colb, null FROM t2;
If we ignore that UNION would remove duplicates (which seems valid to ignore in the case given in the question because 1 can't clash with a DOMAIN\UserName string). EXEC master.sys.xp_cmdshell N'echo 1 & whoami'
This is probably the simplest: CREATE TABLE #x(y nvarchar(max)); INSERT #x(y) EXEC master.sys.xp_cmdshell N'whoami'; SELECT N'1' UNION SELECT y FROM #x WHERE y IS NOT NULL; You might be able to get it done in fewer statements by creating a loopback linked server, but you'll run into all kinds of obstacles - server needs to be configured for data access, ...
You're going to have to insert the results into a temporary table or table variable. You can use INSERT ... EXEC to fill a table with results from a stored procedure call, but that's a special construct. You can't use the results from a stored procedure call as though it's a table in its own right. DECLARE @Result TABLE(ResultData NVARCHAR(255) NULL); ...
Your problem is that xp_cmdshell is a stored procedure. You can do it, and it isn't difficult, but it will require multiple steps. Basically you just have to dump the output of xp_cmdshell (or any other stored procedure) into a temp table, then union that temp table with your query. CREATE TABLE #temp (outputstr varchar(500)) INSERT INTO #temp EXEC ...
Top 50 recent answers are included