2 quickies about SQL queries.

[bennyb220380]

1) Can you use SQL Query Analyser to output selected data from a query to a text file located locally? A little example would be great!

2) I want to use a query to select every 10th record from a table/query, is this possible? If so whats the basic T-SQL for it?

Thanks!!
Ben

 

[SQLBill]

1. Yes, go to Tools > Options > Results. That's one place where you tell QA where to send the output and how to format it.

2. I don't know how to do this, but I'll see if I can find anything.

-SQLBill

 

[fluteplr]

Since tables in sql server have no physical order, define every 10th.

 

[bennyb220380]

Define every 10th:

Their is a datetime column present in the table, so i wold like to select every 10th record in time order.

 

[resdevlabs]

You could use a stored procedure with a temp table and a cursor. That gives you control over which rows you report.

Here's some basic logic:

create table #{tmp_table_name}
   ({column_name} {data_type}
    ...
    )

declare {cursor_name} cursor for
   select {column_list}
     from {time_table}

open {cursor_name}
FETCH NEXT FROM {cursor_name}
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @@ROWCOUNT = 10
   BEGIN
      INSERT INTO #{tmp_table_name}
      VALUES (...)
   END
   FETCH NEXT FROM {cursor_name}
END

CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

SELECT * FROM #{tmp_table_name}

There are 10 types of people in this world. Those who understand binary, and those who don't.

 

[JohnDTampaBay]

Here's a quick and dirty method that doesn't use a cursor:

SELECT IDENTITY(int,1,1) AS ID
  , ColumnList
INTO #tmp
FROM SomeTable
ORDER BY DateColumn

SELECT * FROM #tmp
WHERE ID % 10 = 0

Or if you must use a cursor, here's another way:

CREATE TABLE #tmp
   (Column1     ...)

DECLARE CursorName SCROLL CURSOR FOR
  SELECT ColumnList
  FROM SomeTable
  ORDER BY DateColumn

OPEN CursorName
FETCH ABSOLUTE 10 FROM CursorName
WHILE @@FETCH_STATUS = 0
BEGIN
      INSERT INTO #tmp VALUES (...)
   END

   FETCH RELATIVE 10 FROM CursorName
END

CLOSE CursorName
DEALLOCATE CursorName

SELECT * FROM #tmp

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.