|
As has already been answered above, yes, it is possible. But your design is flawed. It accomodates only 2 products in a category. What if a category has more than two products?
In a real world situation, a category can have hundreds of products and a product can be classified under multiple categories. You should create what is called a bridge table to address this design question.
Create table Product
(
ProductID int PK
)
Create table Category
(
CategoryID int PK
)
Create table CategoryProductBridge
(
CategoryID int FK references Category (CategoryID),
ProductID int FK references Product (ProductID)
)
|
|
|
|
|
You're rather presumptuous. Although there are situations in which products and category have a many to many relationship, there are many real world situations in which a product belows to one category.
|
|
|
|
|
Only issue is the tables are on different servers.
What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file.
I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it.
Any thoughts/ideas would be appreciated.
|
|
|
|
|
You've not mentioned which DB this is for, or if it is for two different vendors perhaps. But I would think for table replication involving 84 million rows, you should be investigating table export/import operations associated with the databases. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Apologise! It is sql server 2005.
|
|
|
|
|
|
Is this a one-time operation? If so, I'd simply backup the database, restore it on the other server, and do a "select into" in the night.
If the tables need to be in sync, I'd probably go for replication.
Bastard Programmer from Hell 
|
|
|
|
|
Backup and restore sounds like a good idea.
Why would I "select * into" again?
Also, I read that "select * into" is faster than "insert into select * from"?
I guess it would one way, since there will no indexes on the table, right?
thanks a lot!
|
|
|
|
|
vanikanc wrote: Why would I "select * into" again?
Also, I read that "select * into" is faster than "insert into select * from"?
I guess it would one way, since there will no indexes on the table, right?
I haven't compared them, just a habit of dumping new data in it's own table. If you want to speed up the copy further, then try to have the "source"-database on another diskdrive then the "destination" database.
Bastard Programmer from Hell
|
|
|
|
|
Problem is with the details.
Is this a one time operation? Is the target under load? Will there need to be a catch up phase (data in source system that did not get moved the first time.)
|
|
|
|
|
If it's going to be a regular operation I open a Connection to each database, use a DataReader to read from one, and ExecuteNonQuery to INSERT into the other. A major benefit of this technique is that it allows the app to periodically log its progress (and speed in rows per minute) so you're not flying blind.
This technique also allows you to catch and ignore selected Exceptions if you like and not have the whole process die.
|
|
|
|
|
Two seperate questions, but apply to the same query, so here goes:
SELECT ProdID, ProdTitle, CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName) AS Author FROM Products LEFT OUTER JOIN ...
This works and returns each item with multiple "author" like:
ProdID Prod Title Author
123 Title 1 John Doe
123 Title 1 Jane Doe
465 Title 2 John Doe
456 Title 2 Jane Smith
But what I want is:
ProdID Prod Title Authors
123 Title 1 John Doe, Jane Doe
465 Title 2 John Doe, Jane Smith
So I add a Group Concat to the Author and i get one line and a BLOB in the AUTHORS
SELECT ProdID, ProdTitle, GROUP_CONCAT(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName)) AS Authors FROM Products LEFT OUTER JOIN...
Question #1 is why does this not work.
Question #2 is how can I use the resulting "Authors" in the WHERE clause against a search string.
Edit 1 ref Question 2: I realize I can redo the concat in the where clause, and that is a fine answer if there are no other options, I just thought you could use the newly created "column" in the where clause directly. Seems cleaner then doing the code twice.
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
modified 29-Jan-12 19:09pm.
|
|
|
|
|
You're not stating what database you're using or what error message you're getting.
GROUP_CONCAT needs a GROUP BY clause in the query. It's unclear if you have one.
|
|
|
|
|
Sorry! Thought the Pre Tags listed the language... Using MySQL.
The group by was the error. I also ended up converting back to chars... For anyone's help, here is the code:
SELECT ProdID, ProdTitle, GROUP_CONCAT(convert(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName), CHAR(24))) AS Authors FROM Products LEFT OUTER JOIN ... GROUP BY ProdID
The second question is more of a best practice I guess. Is there a way to then use the "Authors" list in the where clause? I want the whole list returned, but only with matches to a search string. I know I can duplicate the entire GROUP_CONCAT(convert(CONCAT... but it would be nice if it was only once. Also, will the server actually run those twice? as that seems inefficient.
Thanks again!
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
The answer to #2 is:
HAVING Authors LIKE %SearchString%
Thanks for the help...
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
Can someone help me understand why my urlrewriter is not working? It works fine with one rule, but when I add a second it doesn't recognize the second rule and just sends me back to the homepage (Default.aspx). I've played around with the regular expression in so many ways and nothing seems to work. I've also had the 1st rule as "^~/(\D+)/(.*).aspx"
Here is what I have thus far:
<rewrites>
<add name="Rule1" virtualurl="^~/(Article)/(.*).aspx" destinationurl="~/Article.aspx" rewriteurlparameter="ExcludeFromClientQueryString" ignorecase="true">
<add name="Rule2" virtualurl="^~/(\d+)/(.*).aspx" destinationurl="~/Default.aspx?oid=$1&title=$2" rewriteurlparameter="ExcludeFromClientQueryString" ignorecase="true">
Thanks in Advance!
|
|
|
|
|
sorry, i the original didn't post my code...
<rewrites>
<add name="Rule1" virtualUrl="^~/(Article)/(.*).aspx" destinationUrl="~/Article.aspx" rewriteUrlParameter="ExcludeFromClientQueryString" ignoreCase="true"/>
<add name="Rule2" virtualUrl="^~/(\d+)/(.*).aspx" destinationUrl="~/Default.aspx?oid=$1&title=$2" rewriteUrlParameter="ExcludeFromClientQueryString" ignoreCase="true"/>
</rewrites>
|
|
|
|
|
You should probably repost this one in the Asp.net forum as it doesn't seem to have much to do with databases.
|
|
|
|
|
How to take database backup on online server ?
|
|
|
|
|
Start here[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Simple; you ask the database-administrator. He'll know which database-application is installed. Each manufacturer has it's own implementation, and you can find examples for all major vendors on the internet.
Happy searching 
Bastard Programmer from Hell
|
|
|
|
|
I'm using VB2008 I created a MsAccess 2003 database connection to my project,and make my project exe application. and put it with database Access in one folder and it worked well.
Now I 'm working on the Ms sql server database. I created a DataBase called MyDBs and made connection with it with Vb2008.
My question is can I create Ms Sql database(MyDBs) movable,attachement . means as I did with database access, by put it with application exe in same folder.
I hope I'm clear.
|
|
|
|
|
Yes, by changing the connectionstring[^]. Look under the header "Attach a database file on connect to a local SQL Server Express instance". Your customers will still need to have Sql Server installed, regardless.
If your database is local to the app (say, in the same folder) then stick with Access, or go for SqlCe. If your database needs to support more than a single user, you use Sql Server - in a centralized place where everybody who needs to access the database, can access it.
Bastard Programmer from Hell
|
|
|
|
|
I made this for dtatbase: Script database as - CREATE TO - File
and took the file Of DataBase(MyDBs) and put it in Debug folder in my project.
then I tried this way but didn't succed with me.
Dim MyConnection As SqlConnection = New SqlConnection("Data Source=.\SQLExpress; Integrated Security=true; AttachDbFilename=|DataDirectory|\MyDBs.sql;")
Public Sub Load_DB()
Dim sql As String = " Select Name,Tel,Addr from Table1 "
Dim dp As New SqlDataAdapter(sql, MyConnection)
Dim Table1 As New DataTable
dp.Fill(Table1)
Form1.DataGridView1.DataSource = Table1
End Sub
|
|
|
|
|
Mangore75 wrote: then I tried this way but didn't succed with me.
HOW didn't is succeed? What EXACTLY happened so you know it didn't work?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
