Splitting Table based on field

[Karl Blessing]

This was also posted in the VB Forum since it deals partly with VB coding, but I seek a SQL Query solution

At the moment I have a small demostration table that has been precreated, it contains about 1,500 records as a demo (the actual table contains over 25,000 records)

anyways the table is setup as so...


SQL Query used to get table is: select * from empdb_bit order by sic2, sic4

Sic2 ¦ Sic4 ¦ latbit ¦ lngbit ¦ ct ¦ id

the goal is of course to create new tables with the names of the Sic2 (like sic02) and each new table (or file as we want to create them into binary files later on) will look like:

Sic4 ¦ Lat ¦ Lng ¦ ID

also the new table format above, must be sorted by Sic4 (which I could probally do just by saying order by Sic4)

at the moment I have this code to identify the different Sic2:


Dim TmpSic As String
Label1.Caption = ""
With Adodc1.Recordset
.MoveFirst
If Not .EOF Then
TmpSic = .Fields("Sic2&quot
While Not .EOF
If TmpSic <> .Fields(&quot;Sic2&quot Then
Label1.Caption = Label1.Caption & vbCrLf & TmpSic
TmpSic = .Fields(&quot;Sic2&quot
End If
.MoveNext
Wend
Label1.Caption = Label1.Caption & vbCrLf & TmpSic
End If
End With


but it's incredibly slow for the number of records. is there perhaps a SQL Statement, that can least help speed up the process? I am using SQL Server 6.5

 

[bigscouse]

To create a table of format: -
Sic4 ¦ Lat ¦ Lng ¦ ID

The SQL is: -

CREATE TABLE sic04 AS
SELECT sic4, lat, lng,id
FROM empdb_bit
ORDER BY sic4;
COMMIT;

 

[Karl Blessing]

ok but do you know of a way where i can make sql automatically split based on different Sic2.

 

[bigscouse]

CREATE TABLE sic04 AS
SELECT sic4, lat, lng,id
FROM empdb_bit
WHERE sic2 = &quot;sic02&quot;
ORDER BY sic4;
COMMIT;

 

[Karl Blessing]

ok that takes care of creating the suckers when I figure out what numbers to use. but you do know that I'm hopeing there is a way to take the seperate groups , then create tables or files for them in one pass?

like the sic2 could be 1 or 58, or 23 and I'd have to create a seperate file for each.

 

[MalcolmW]

Karl,
Have you tried something like this?
Sorry, don't have time to check to see if this works, this is just what occured to me...buyer beware!

select *
into #temp_empdb_bit
from empdb_bit

declare @sic2 int

While select count(*) from #temp_empdb_bit > 0
begin
select @sic2 = (select top 1 sic2 from #temp_empdb_bit)
exec 'CREATE TABLE Split_'+ CAST(@sic2 AS varchar(25)) +
' SELECT sic4, lat, lng, id
FROM empdb_bit
WHERE sic2 = @sic2
ORDER BY sic04'
delete from #temp_empdb_bit
SELECT sic4, lat, lng,id
FROM empdb_bit
WHERE sic2 = @sic2
ORDER BY sic04
end

 

[Karl Blessing]

I appreciate your response, however I have made it down into where it just grabs the recordset at where sic2 = whatever, and writes it to a file, we cannot do Select * , and not use a Where clause, as the empdb_map4 is way too large, and times out in every known Database interaction.