I am used to working in an Intranet where I have access to all the
pieces, but now I am working with the Internet and am stuck. I receive
various datafeeds in the form of a CSV file and need to get that data
into a SQL Server database on my host. On my development machine I put
it in Access for testing my code. But now that I am done testing I
have to get the data to the real database online.
I don't know how to get the access/CSV data into Sql Server on the
Internet. I'm not sure where to start.
Thank you for any help.Hi,
it depends on how your mechanism is to import the data into the
database. If you use the enterprise Manager and DTS to import, just try
to do the same as on your current server, trying to host your file
somewhere and specify it with the http adress where it is hosted on.
(Don=B4t know if htp is supported in DTS)
Another option would be to copy the file via FTP to your server and
specify the local path for importing the data, wheter using DTS or a
linked server to import the data. There are several options to do this,
but its hard to evaluate one without knowing more details.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi
One method would be to have a scheduled job that runs a DTS package that
will check for files in a give directory and then load them up. You could use
FTP (possibly through a ASP component) to move your files to the location. If
you schedule you job to run periodically then it could pick up the file
without further intervention or you could start the job off manually (say
through a button on your ASP page). As the location that the file is
deposited is probably on the IIS server and not the database server, you will
need a method that will securely allow your database server to see this
directory without compromising the database server.
John
"needin4mation@.gmail.com" wrote:
> I am used to working in an Intranet where I have access to all the
> pieces, but now I am working with the Internet and am stuck. I receive
> various datafeeds in the form of a CSV file and need to get that data
> into a SQL Server database on my host. On my development machine I put
> it in Access for testing my code. But now that I am done testing I
> have to get the data to the real database online.
> I don't know how to get the access/CSV data into Sql Server on the
> Internet. I'm not sure where to start.
> Thank you for any help.
>|||Do you mean ftp the local .mdf and .ldf files from my development
machine to my Internet server?|||Hi
May be look at replication?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replsec_5ad0.asp
John
"needin4mation@.gmail.com" wrote:
> Do you mean ftp the local .mdf and .ldf files from my development
> machine to my Internet server?
>
Showing posts with label various. Show all posts
Showing posts with label various. Show all posts
Monday, March 19, 2012
Friday, February 24, 2012
How do I do categories and subcategories and more subcategories?
Hi, I have a database that will have various categories:
take this:
Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas
(it's shoes)
I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.
-PatP|||There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.
-PatP
Thanks. So how would that string I put in my OP fit into the table? What would it look like?|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP
I know you have been more than gracious, but how would you select it back out? Also, i am thinking that in my shoe table that actually holds the shoes I would have a column like "shoe_category" that would hold the last level (here it is 5. Then I would
select * from categories where categoryid = 1 and parentcategoryid = categoryid
but that didn't yield any rows. but that didn't make sense to me anyway that it should (rambling out loud). How can I get that back out. I'm drawing, yet another, blank.
I also tried something like:
select * from categories c, categories c1
where c.categoryid = 1
and
c.categoryid = c1.parentcategoryid
but didn't get the right thing.|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).
Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!
-PatP|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).
Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!
-PatP
I was thinking I would want each level as requested by the user. They might click "kids" and it brings back one level down and next to that level there would be a dropdown containing all the other items at that level. For example, at that level, there may be kids -> boys -> canvas, or european, or athletic..., so yes, your initial email was right on target. It is difficult to imaging or retrieve the branches. I will think about some more.
Does this type of table have an "official" name, this structure? I could look in a book at various examples to help me or at least search for it by the correct type. Thanks for your help.|||Does this type of table have an "official" name, this structure? adjacency model, or adjacency list model
take this:
Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas
(it's shoes)
I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.
-PatP|||There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.
-PatP
Thanks. So how would that string I put in my OP fit into the table? What would it look like?|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP
I know you have been more than gracious, but how would you select it back out? Also, i am thinking that in my shoe table that actually holds the shoes I would have a column like "shoe_category" that would hold the last level (here it is 5. Then I would
select * from categories where categoryid = 1 and parentcategoryid = categoryid
but that didn't yield any rows. but that didn't make sense to me anyway that it should (rambling out loud). How can I get that back out. I'm drawing, yet another, blank.
I also tried something like:
select * from categories c, categories c1
where c.categoryid = 1
and
c.categoryid = c1.parentcategoryid
but didn't get the right thing.|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).
Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!
-PatP|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).
Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!
-PatP
I was thinking I would want each level as requested by the user. They might click "kids" and it brings back one level down and next to that level there would be a dropdown containing all the other items at that level. For example, at that level, there may be kids -> boys -> canvas, or european, or athletic..., so yes, your initial email was right on target. It is difficult to imaging or retrieve the branches. I will think about some more.
Does this type of table have an "official" name, this structure? I could look in a book at various examples to help me or at least search for it by the correct type. Thanks for your help.|||Does this type of table have an "official" name, this structure? adjacency model, or adjacency list model
Subscribe to:
Posts (Atom)