Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-01-30 : 08:42:03
|
Hi,Running a query via BCP from a batch file, I'm trying to select the top 10 rows from a table, output them to a file, and then select the NEXT 15 and output those to another file. I googled an EXCEPT command and tried to use it but got an "incorrect syntax" message. Is there another way I can do this ?Thanks,Jeff |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-30 : 08:45:10
|
insert into #temp ( . . .)select top 25 . . .from tableorder by somecol-- top 10select top 10 . . .from #temporder by somecol-- top 15select top 15 . . from #temporder by somecol descorder by somecol KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 08:47:19
|
INSERT the data you want to export into a temporary table with an IDENTITY(0, 1) column named RowID.Now you can export the first 10 records withSELECT * FROM #Temp WHERE RowID < 10Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (3, 4, 5)Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (6, 7, 8)Peter LarssonHelsingborg, Sweden |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-01-30 : 08:47:49
|
Thanks khtan ! I will try this.J |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-01-30 : 13:42:31
|
Thanks Peter !Could you explain a little more about what your second SELECT is doing ?SELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (3, 4, 5)I don't understand the "WHERE RowID % 5 IN (3, 4, 5)" part of it.Thanks,J |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-01-30 : 14:04:08
|
Peter,I created that temp table with the IDENTITY and tried to insert the data but got this message:An explicit value for the identity column in table '#temp_create_batch_jeff' can only be specified when a column list is used and IDENTITY_INSERT is ON.This is the first two lines of the create table:CREATE TABLE [#temp_create_batch_jeff] ( [RowID] [int] IDENTITY (0, 1) NOT NULL ,Thanks,J |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-30 : 14:30:42
|
You must not insert a value into the RowID column yourself because it is an identity (auto incrementing number) column. Here is an example selecting rows from the Northwind Customers table (note that the select does not have a column for the RowID).CREATE TABLE [#temp_create_batch_jeff] ([RowID] [int] IDENTITY NOT NULL ,CompanyName varchar(50))INSERT #temp_create_batch_jeff (CompanyName)SELECT CompanyName FROM Northwind..Customers ORDER BY CompanyNameSELECT * FROM #temp_create_batch_jeff WHERE RowID <= 10SELECT * FROM #temp_create_batch_jeff WHERE RowID BETWEEN 11 AND 25SELECT * FROM #temp_create_batch_jeff WHERE RowID BETWEEN 26 AND 40DROP TABLE [#temp_create_batch_jeff] Peter's code is using the modulus operator (%) to test RowIds by dividing by 5 and taking the remainder, but I think using a simple BETWEEN as in my example is as fast and simpler in your case. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 14:34:53
|
My typo...% should be / Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 14:42:33
|
INSERT the data you want to export into a temporary table with an IDENTITY(5, 1) column named RowID.SELECT Col1, Col2, ColX, IDENTITY(INT, 5, 1) AS RowID INTO #Temp FROM YourOriginalTableNameHereORDER BY ....1)Now you can export the first 10 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 02)Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 1 --<-- filenumber3)Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 2 --<-- filenumber4)And the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 3 --<-- filenumberWith this technique, it will be very easy with a WHILE to create new filenames for every loop and export data according to "loop counter".Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 15:30:46
|
Or, if you are having trouble with zero-based counters:INSERT the data you want to export into a temporary table with an IDENTITY(20, 1) column named RowID.SELECT Col1, Col2, ColX, IDENTITY(INT, 20, 1) AS RowID INTO #Temp FROM YourOriginalTableNameHereORDER BY ....1)Now you can export the first 10 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 1 --<-- filenumber2)Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 2 --<-- filenumber3)Then you can export the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 3 --<-- filenumber4)And the next 15 records withSELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 4 --<-- filenumberWith this technique, it will be very easy with a WHILE to create new filenames for every loop and export data according to "loop counter".Peter LarssonHelsingborg, Sweden |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2007-01-31 : 13:59:53
|
Thanks very much Peter !J |
|
|
|
|
|
|
|