Sunday, September 15, 2013

[SQL Server 2008 issues] Give priority to union statements

[SQL Server 2008 issues] Give priority to union statements

Give priority to union statements

Posted: 14 Sep 2013 06:55 PM PDT

Hi, if I have several SELECT statements with UNION how can I tell to sql to process which one first?Please take a look at this structure:[code="sql"]CREATE TABLE #table1 (ID INT, Title1 NVARCHAR(1), Title2 NVARCHAR(1));INSERT INTO #table1 VALUES(1,'A','B')INSERT INTO #table1 VALUES(2,'C','B')[/code]Now I wish to INSERT the values from #table1 to my CTE with this query:[code="sql"]WITH CTE (ID,Title) AS ( SELECT ID, Title1 FROM #Table1 --I expect this query run first UNION ALL SELECT ID, Title2 FROM #Table1 --I expect this query run after above query)SELECT * FROM CTE[/code]But result is something different CTE sort my table automatically and give me this output:ID,Title---------1,'A'1,'B'2,'B'2,'C'But my query should have this output:ID,Title---------1,'A'1,'B'2,'C'2,'B'How can I generate the output I want?Thank you for help

Sql force me to include Row_Number() in group by

Posted: 14 Sep 2013 05:07 PM PDT

Hi, this is my table structure:[code="sql"]CREATE TABLE #table1 (ID INT, Col1 NVARCHAR(1), Col2 INT);INSERT INTO #table1 VALUES(1,'B',100);INSERT INTO #table1 VALUES(2,'A',200);INSERT INTO #table1 VALUES(3,'B',300);INSERT INTO #table1 VALUES(4,'C',400);[/code]I want to have SUM(Col2) GROUPED BY(Col1) and a ROW_NUMBER() ORDERED BY (ID)Here is my query:[code="sql"]WITH CTE(Col1, Col2, Row_Num) AS ( SELECT Col1 ,SUM(Col2), ROW_NUMBER() OVER(ORDER BY (ID)) FROM #table1 GROUP BY Col1, ID -- here SQL force me to include ID and it generate incorrect output)SELECT * FROM CTE[/code]Here is my output:col1,col2,Row_Num-------------------B,100,1A,200,2B,300,3C,400,4But I want this output:col1,col2,Row_Num-------------------B,400,1A,200,2C,400,3Thank you for help

Group by without soring

Posted: 13 Sep 2013 11:49 PM PDT

Hi, this is initializations:[code="sql"]CREATE TABLE #table1(col1 nvarchar(1), col2 int);INSERT INTO #table1 VALUES('B','100');INSERT INTO #table1 VALUES('A','200');INSERT INTO #table1 VALUES('B','300');INSERT INTO #table1 VALUES('C','400');[/code]Here is my query:[code="sql"]WITH CTE (col1,col2) as ( SELECT col1, SUM(col2) FROM #table1 GROUP BY col1)SELECT * FROM cte[/code]Here is output:col1,col2----------------A,200B,400C,400Why GROUP BY statement sort my table automatically?I want to display my table as it is.Is it possible?Thank you very much for help.

Concat column values

Posted: 14 Sep 2013 04:06 PM PDT

I have following table[code="sql"]DepartmentID Employee------------- ---------1 John1 Michael2 Jenson2 Lily2 Lara[/code]I want the output in following format[code="sql"]DepartmentID Employees------------- ----------1 John,Michael2 Jenson,Lily,LaraPlease provide examples[/code]

Union overwrite null values

Posted: 14 Sep 2013 04:00 AM PDT

Is there a way using union to overwrite null values?for example:I hava two views with following columnsatt_date, student_id, AM_attendance code , PM_AttendanceCodethe union is:select '9/8/2013', 12345, 'EA', Nullunionselect '9/8/2013', 12345, Null, 'UA'I would like the result be one row instead of the two rows, the null values to be overwritten.'9/8/2013', 12345, 'EA', 'UA'

rebuild index for a table failed

Posted: 14 Sep 2013 12:30 PM PDT

We had found logical consistency errors in the database when we ran dbcc checkdb.The application vendor told us to rebuild an index for a particular table but we ended up with the following error.Any further way out:Executing the query "ALTER INDEX [PK__ERRORLOG] ON [dbo].[GSX_ERRORL..." failed with the following error: "SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x10001555). It occurred during a read of page (1:9381968) in database ID 7 at offset 0x000011e50a0000 in file 'P:\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\GSX_Prod_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can concurrent INSERTs mess up a UNION?

Posted: 27 Jan 2013 04:13 PM PST

Can SQL Server guarantee that if I issue a command like:[code="sql"]INSERT INTO T(...)SELECT t.a, t.b, 1FROM sometable tUNIONSELECT -t.a, t.b, 1FROM sometable t[/code], both "sides" of union will retrieve same table rows?

Send multiple HTML tables with sp_send_dbmail

Posted: 29 Apr 2010 11:35 PM PDT

How would someone send multiple html tables with sp_send_dbmail? In the @body paramater I can only send one table such as something like @tableHTML. I would like to be able to send more than one table in the email such as @tableHTML2 or something of the sort to display the extra table below the first one. Has anyone had experience in doing this and can advise?

Mapping inserted / source identity values

Posted: 14 Sep 2013 05:16 AM PDT

Hi,I have an interesting problem that i haven't been able to find a good solution for.What i want to do is to make an insert from table source into destination and find out the mapping between the source and destination table rows.The problem is that I cannot match the non-identity values on each other because they're no uniqueness between them. In real world, it's actually adress logging tables.Here are the sample script:[code="sql"]CREATE TABLE [source] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [destination] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [mapping] (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column-- Import some sample data...INSERT INTO [source] (some_value)SELECT TOP 30 nameFROM sysobjects-- Destination has some data alreadyINSERT INTO [destination] (some_value)SELECT TOP 30 nameFROM sysobjects--Here, i want to transfer everything from source into destination, but be able to keep a mapping on the two tables:-- I try to use OUTPUT clause, but i cannot refer to columns outside of the ones being inserted:INSERT INTO [destination] (some_value)--OUTPUT inserted.i, s.i INTO [mapping] (i_new, i_old) -- s.i doesn't work!!SELECT some_valueFROM [source] s[/code]I cannot change table definition, and i'd rather not mess with SET IDENTITY_INSERT ON either...Does someone have an idea?

Job Details

Posted: 14 Sep 2013 12:38 AM PDT

How to get job schedule details, I mean detailed schedule like, all the jobs on all 7 days. Sun Mon TueXXXXXX job - runs at 9 am no run runs at 9 am so what i require is list lal the jobs and its run/not run schedule for every 7 days. Does anyone have the query?

Optimization with multiple Select and Update statements

Posted: 13 Sep 2013 11:38 PM PDT

Hi,If I have multiple select and update statements in my stored procedure, will it be better to group up the select statements follow by the update statements like follows:[code="plain"]SELECT ...SELECT ...SELECT ...SELECT ...UPDATE ...UPDATE ...UPDATE ...UPDATE ...[/code]Or, will the performance be better if I will to group the SELECT and UPDATE as a pair like follows:[code="plain"]SELECT ...UPDATE ...SELECT ...UPDATE ...SELECT ...UPDATE ...SELECT ...UPDATE ...[/code]

How to find a table

Posted: 13 Sep 2013 07:17 PM PDT

I want to find the table name where there is a column 'WS_ID' .Can you please tell me how do find this table name ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog