[ANSWERED] Group_Concat and using a query result in the where clause - Database Discussion Boards

Re: Copy 84 million rows from one table to another table.

vanikanc30-Jan-12 4:50

vanikanc

30-Jan-12 4:50

Apologise! It is sql server 2005.

Re: Copy 84 million rows from one table to another table.

RK KL30-Jan-12 5:06

RK KL

30-Jan-12 5:06

I would use incremental loading approach as shown below. It has worked well for me for about 2m rows.

http://sqlblog.com/blogs/andy_leonard/archive/2011/12/21/1631.aspx[^]

Good luck.

You can never try. You either do it or you don't.

Re: Copy 84 million rows from one table to another table.

Eddy Vluggen30-Jan-12 5:34

Eddy Vluggen

30-Jan-12 5:34

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 Suspicious | :suss:

Re: Copy 84 million rows from one table to another table.

vanikanc30-Jan-12 5:39

vanikanc

30-Jan-12 5:39

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!

Re: Copy 84 million rows from one table to another table.

Eddy Vluggen30-Jan-12 7:01

Eddy Vluggen

30-Jan-12 7:01

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 Suspicious | :suss:

Re: Copy 84 million rows from one table to another table.

jschell30-Jan-12 9:12

jschell

30-Jan-12 9:12

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.)

Re: Copy 84 million rows from one table to another table.

PIEBALDconsult31-Jan-12 2:59

PIEBALDconsult

31-Jan-12 2:59

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.

[ANSWERED] Group_Concat and using a query result in the where clause

Dwayner7929-Jan-12 7:35

Dwayner79

29-Jan-12 7:35

Two seperate questions, but apply to the same query, so here goes:

SQL

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

SQL

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.

Re: Group_Concat and using a query result in the where clause

Jörgen Andersson29-Jan-12 8:35

Jörgen Andersson

29-Jan-12 8:35

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.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Group_Concat and using a query result in the where clause

Dwayner7929-Jan-12 11:24

Dwayner79

29-Jan-12 11:24

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:

SQL

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

Re: Group_Concat and using a query result in the where clause

Dwayner7929-Jan-12 13:01

Dwayner79

29-Jan-12 13:01

The answer to #2 is:

SQL

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

URL rewriting using urlrewriternet not working

Timothy OBrien28-Jan-12 12:17

Timothy OBrien

28-Jan-12 12:17

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!

Re: URL rewriting using urlrewriternet not working

Timothy OBrien28-Jan-12 12:19

Timothy OBrien

28-Jan-12 12:19

sorry, i the original didn't post my code...

HTML

<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>

Re: URL rewriting using urlrewriternet not working

Jörgen Andersson29-Jan-12 8:07

Jörgen Andersson

29-Jan-12 8:07

You should probably repost this one in the Asp.net forum as it doesn't seem to have much to do with databases.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

How to take database backup on online server ?

infobeena28-Jan-12 1:49

infobeena

28-Jan-12 1:49

How to take database backup on online server ?

Re: How to take database backup on online server ?

R. Giskard Reventlov28-Jan-12 5:02

R. Giskard Reventlov

28-Jan-12 5:02

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

Re: How to take database backup on online server ?

Eddy Vluggen28-Jan-12 5:25

Eddy Vluggen

28-Jan-12 5:25

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Create Database Ms SQl server

Mangore7527-Jan-12 10:42

Mangore75

27-Jan-12 10:42

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.

Re: Create Database Ms SQl server

Eddy Vluggen27-Jan-12 23:51

Eddy Vluggen

27-Jan-12 23:51

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 Suspicious | :suss:

Re: Create Database Ms SQl server

Mangore7528-Jan-12 3:25

Mangore75

28-Jan-12 3:25

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

Re: Create Database Ms SQl server

jschell28-Jan-12 4:47

jschell

28-Jan-12 4:47

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?

Re: Create Database Ms SQl server

Mangore7528-Jan-12 4:57

Mangore75

28-Jan-12 4:57

it showed me this error;
An attempt to attach an auto-named database for file C:\Users\Nader\Desktop\OOP- CreatProject\OOP- CreatProject\bin\Debug\MyDBs.sql failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Re: Create Database Ms SQl server

jschell28-Jan-12 13:58

jschell

28-Jan-12 13:58

Error seems to be specifically telling what the possible problems are.

Re: Create Database Ms SQl server

Eddy Vluggen29-Jan-12 0:16

Eddy Vluggen

29-Jan-12 0:16

Only one instance with the name of the database is allowed on the server. Detach the database that's connected, or use an other name.

Bastard Programmer from Hell Suspicious | :suss:

Re: Create Database Ms SQl server

Eddy Vluggen28-Jan-12 5:22

Eddy Vluggen

28-Jan-12 5:22

Why did you expect it too succeed? Smile | :)

Sql isn't the same beast as Access. You'll need to have a server installed, connect to that, and *hope* that you have sufficient rights to create a database on that server.

Is there a server installed? Can you connect to the master-database on "127.0.0.1\SqlExpress"?

Bastard Programmer from Hell Suspicious | :suss:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.