Help with an import to SQL

Hi.

Please try to bear with this SQL "newbie" here ;)

I am trying to figure out how to import the FCC ULS report results into a
database. I believe what I need is a JOIN statement. To see the data
for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt

(I chose this query because it's near where I live. If you want to run
your own search, the URL is
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)

(NOTE:
The FCC webpage with the query at
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
what I am trying to do, but I'd like to keep just certain records locally
so that searches are faster and are not dependent on being on a computer
with an internet connection.)

I'll try to briefly explain what you are seeing.

The .txt file referenced above is a flat text file, pipe-delimited.

The very first field is a two-letter code denoting the table type. Some
examples are HD (Header), FR (Frequency), and EM (Emission Type).

The second field is a unique entity number.

The fourth field contains the call sign (WNDV619).

After that, the content varies significantly, based on the first field's
table type.

The FCC website lists the column headings for each table type. For
example, in a HD (Header) table, the first field is the table type, the
second is the entity number, the fourth field is the call sign, the
seventh field is some date, etc. The SQL statements (I believe there are
some syntax errors) to create all of those table types can be found at
http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql

Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
bin/wtb-datadump.pl

(This is so overwhelming...)

What I would like to do is (either in Access if possible--yeah,right--or
via a web-type query in .asp or .html) to be able to click on a call
sign, and have a new table appear with choices of table types, then to be
able to click on the table type, and have a new table appear with those
choices, etc.

Some entity numbers have more than one call sign. Some call signs have
more than one frequency (as in the example above), etc.

Is there an explanation of how to do this already? (What terminology
describes that which I am trying to accomplish? Linked tables?)

Note that with the number of table headers available (that first column,
2-letter code) there can be quite a few tables just on one entity number
and/or call sign.

Can anybody help?

--
NOTE: To reply, DELETE the obvious word in my e-mail address you need to
DELETE in order to reply.One option is to create a different format file for each table type
code. Then, using bulk insert or bcp, you can do multiple passes and
each time load a particular data file to its corresponding table.
You can also try to load the text file into a staging area. You may
then process each row in that table through a client app or from a
stored procedure.

As far as your other questions are concerned, you can check Books
Online and other documentation on primary keys, foreign keys,
normalization, etc.

TeleTech <tele_tech1212DELETE@.yahoo.com> wrote in message news:<Xns950087502A22Bteletech1212@.207.69.154.205>...
> Hi.
> Please try to bear with this SQL "newbie" here ;)
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt
> (I chose this query because it's near where I live. If you want to run
> your own search, the URL is
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)
> (NOTE:
> The FCC webpage with the query at
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
> what I am trying to do, but I'd like to keep just certain records locally
> so that searches are faster and are not dependent on being on a computer
> with an internet connection.)
> I'll try to briefly explain what you are seeing.
> The .txt file referenced above is a flat text file, pipe-delimited.
> The very first field is a two-letter code denoting the table type. Some
> examples are HD (Header), FR (Frequency), and EM (Emission Type).
> The second field is a unique entity number.
> The fourth field contains the call sign (WNDV619).
> After that, the content varies significantly, based on the first field's
> table type.
> The FCC website lists the column headings for each table type. For
> example, in a HD (Header) table, the first field is the table type, the
> second is the entity number, the fourth field is the call sign, the
> seventh field is some date, etc. The SQL statements (I believe there are
> some syntax errors) to create all of those table types can be found at
> http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql
> Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
> bin/wtb-datadump.pl
> (This is so overwhelming...)
> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.
> Some entity numbers have more than one call sign. Some call signs have
> more than one frequency (as in the example above), etc.
> Is there an explanation of how to do this already? (What terminology
> describes that which I am trying to accomplish? Linked tables?)
> Note that with the number of table headers available (that first column,
> 2-letter code) there can be quite a few tables just on one entity number
> and/or call sign.
> Can anybody help?|||TeleTech (tele_tech1212DELETE@.yahoo.com) writes:
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt

I'm afraid that importing this data is not entirely trivial matter.
The way I would do it would be to write a Perl script that read
one row at a time, splitted the data into columns, looked at the first
field to find the target table.

The reason I would use Perl is simply because this is the client language
I know best. You should be able to this in VBscript, C++ or whatever.

I don't know if DTS has some built-in support for this kind of file, but
I doubt. But since I don't know DTS, don't take it from me. The friendly
folks in microsoft.public.sqlserver.dts would know for sure.

> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.

I'm not really sure what sort of help you are asking for here, but it
sounds like something which is more apt for comp.databases.ms-access or
some web-programming newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Help with a trigger

Hello
Im am a sql newbie who has a question concerning triggers.
Ive made this trigger:

create trigger check_if_already_exists on
users
for insert ,update
as
begin
begin transaction

Declare @.Username varchar(20)
Declare @.Username_exists varchar(20)

select @.Username = user_id
from inserted

set @.Register = cursor scroll dynamic
for select user_id,
from users
order by user_id

open @.Register

fetch next from @.Register into @.Username_exists

while @.@.fetch_status = 0
begin

if(@.Username = @.Username_exists)

begin
rollback transaction
print'Transaction rollback'
end

fetch next from @.Register into @.Username_exists

end

close @.Register
deallocate @.Register

commit transaction
end

which takes a variable from inserted and checks it with the existing one
in the database through a cursor.

My questions are
1)
to start and end the trigger should i use:
begin transaction -- end transaction | commit transaction

or
begin -- end

2)
Im using a cursor here , is this a good use of cursors

Mike[posted and mailed, please reply in public]

(timmy_dale12@.hotmail.com) writes:
> select @.Username = user_id
> from inserted

Note that since a trigger fires once per statement, the inserted table
can hold more than one row. Only getting one value to a variable is
not a good thing.

> to start and end the trigger should i use:
> begin transaction -- end transaction | commit transaction

When you detect an error situation, you should issue a ROLLBACK TRANSACTION.
You should not fiddle with BEGIN/COMMIT TRANSACTION in a trigger. A trigger
always executes in the context of a transaction, as it is part of a INSERT,
DELETE or UPDATE statement and such a statement always starts a transaction,
if there is no transaction already active.

> Im using a cursor here , is this a good use of cursors

No, it is not.

There are actually a whole bunch of problems with your trigger, and which
leads to that this trigger should not exist at all.

Let's first look at the test you should make. This is a set-based version
of your cursor loop which covers all inserted rows:

IF EXISTS (SELECT *
FROM inserted i
JOIN users u ON i.user_id = u.user_id)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('One or more inserted users does already exist', 16, -1)
RETURN
END

Note here that I use RAISERROR rather than PRINT. This is because I
want the client to beware of that there was an error.

However, since all rows in inserted at this point also are in users,
this check is always going to be true, so you will always get an error
message. Thus, you cannot implement this check in a trigger at all.
Rather you should have a UNIQUE or PRIMARY KEY constraint on the user_id
column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> IF EXISTS (SELECT *
> FROM inserted i
> JOIN users u ON i.user_id = u.user_id)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('One or more inserted users does already exist', 16, -1)
> RETURN
> END
> Note here that I use RAISERROR rather than PRINT. This is because I
> want the client to beware of that there was an error.

How can i get the error message that the RAISERROR method generates,
is it returned in any way.
For example if i use a java application can i catch this in a try
block:

try {

......
}catch(SQLException e){
e.toString();
}|||(timmy_dale12@.hotmail.com) writes:
> How can i get the error message that the RAISERROR method generates,
> is it returned in any way.
> For example if i use a java application can i catch this in a try
> block:
> try {
> ......
> }catch(SQLException e){
> e.toString();
> }

That depends on the client library you are using. Since all I know about
Java is that it lies in the vicinity of Sumatra, I cannot say for sure
what happens, but I would expect an exception to be thrown, yes. (Provided
that you for the severity level specify 11 or higher.)

What can be puzzling is if the SQL code generates result sets, before
the RAISERROR statement, then you need to get past the result sets before
the exception is thrown. (Whether this applies to Java, I don't know,
but it happens with ADO.) Furthermore, there are more result sets you
may expect, because these "2 rows affected" you can see in Query Analyzer
from an INSERT, UPDATE or DELETE statement is also some sort of result
set.

SET NOCOUNT ON removes these "result sets", and is generally good for
performance, so use this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help w/ SQL Newbie Student

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks

... I have done the frist 49 of the queries ... and they all made sense ... but this one is just bugging me ... PLEASE HELPDon't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman|||Originally posted by blindman
Don't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman

The assignment had 50 queries ... of all different kinds.

We have to do it in a single SELECT statement (with subqueries of course)

I do not think that an table except the SUPPLY and ITEM are needed but I dont know exactly how they interact ...|||If your answer must be a single select statement, then that eliminates any possibility of searching the departmental hierarchy, and so actually makes the problem easier.

Break your problem into parts.

If you had a list of companies (departments?) then it would be trivial to list all the products they sell, right?

So your task is to create a subquery that lists all the departments that sell all type "A" products. How do you determine if a department sells all of the type "A" products?

The most direct method would be to find Departments where the number of distinct type "A" products they sell equals the number of type "A" products that exist.

The more subtle method (which your code seems to be attempting) is to create a list of departments that DON'T sell all the type "A" items, and then find all the departments that AREN'T on that list. This is certainly a more confusing method than the product count technique, but it may have some efficient advantages for extremely large tables. I don't know. But I do know you can get a list of departments that don't sell a full line of type "A" products by using a FULL OUTER JOIN between DEPARTMENT and a list of type "A" suppliers.

blindman