merging two tables or two datasets - Database Discussion Boards

Minoo S2-Feb-06 17:31

2-Feb-06 17:31

Thanx for your response Colin.

Scenarios : When a call is placed, it writes to Phone Log. After that logic goest to the Survey software and writes the information in Survey log. After writing in the survey log, the interviewer gets to talk to the respondent.

The information I am pulling out is from Phone log and Survey log. There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa. So while matching I need to have left and right join information as well. If I cannot resolve in sql, then may be I should work with some scripting langauge and write a prg to match. Can you think of any prg lang I could do this real quick ??

Minoo S

Re: Need help for sql ASAP Phuleez

Colin Angus Mackay2-Feb-06 20:58

Colin Angus Mackay

2-Feb-06 20:58

Minoo S wrote:
There is more complexity to the case as there could be a row in Phone log that does not exist in Survey log and vice versa.

Okay - but that doesn't seem to excuse not having proper primary / foreign keys. Two times *close* to each other really does not cut it. For a database to join things up properly you need to be precise.

Minoo S wrote:
may be I should work with some scripting langauge and write a prg to match.

That would be slow. Scripting languages are useless for this sort of thing.

Minoo S wrote:
Can you think of any prg lang I could do this real quick ??

SQL - But only if your database is designed right in the first place.

Some questions:

What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one

(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Need help for sql ASAP Phuleez

Minoo S3-Feb-06 7:16

Minoo S

3-Feb-06 7:16

Thanx Colin . I will try to answer your questions.

Some questions:
What is the relationship between PhoneLog and SurveyLog? one-to-many, many-to-many, many-to-one, one-to-one

Sorry, I missed response on this one, its one to one relationship. So one entry in Phone can have only one entry in Survey LOG . Just that Phone log might have entries that were not recorded in Survey log and vice versa.

(This is a repeat of something I asked before - I really would like to know because it will help solve your problem) What are the primary keys of each of these tables as it stands now?

Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ?? Presently , I parsed the logs and pulled out information and did not set Primary key. I understand it is gross mistake and may be main cause of the issue in first place ! I thot I could make CaseID and timestamps together but then we cannot use it as foreign key as the timestamp will not match in Survey table. Any ideas ?

Minoo S

Re: Need help for sql ASAP Phuleez

Minoo S5-Feb-06 8:43

Minoo S

5-Feb-06 8:43

HI ! Any clue from this point ?? Need more info ??

Minoo S

Re: Need help for sql ASAP Phuleez

Colin Angus Mackay5-Feb-06 8:47

Colin Angus Mackay

5-Feb-06 8:47

Minoo S wrote:
Any clue from this point ??

I thought you'd figured it out already:

Previously Minoo S wrote:
Primary Key : I will have to put ID to make the rows unique,I think that might join the rows mentioned in original examples correctly, if I index by unique rows ??

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Need help for sql ASAP Phuleez

Minoo S19-Apr-06 6:14

Minoo S

19-Apr-06 6:14

Thx for your help on this one. My new question (calculations has the correct table structure, thx to you. Sorry on forgetting the rating on your response, one the issue was resolved, I was on another fast burn project !

Minoo S

Creating SQL Blocks?

Justin Cooke2-Feb-06 4:36

Justin Cooke

2-Feb-06 4:36

Hi All,

We use MOM (Microsoft Operations Manager) software to monitor our systems. One problem it alerts us to is SQL blocks. However, it's too noisy and I need to tweak the alert rule. To make my testing easier, I'd like to be able to simply start and stop SQL blocking at my command (rather than wait for usage blocks to occur).

I'm not a SQL expert (by any stretch Smile | :)

) and I've been looking for a script I could run (maybe in two QueryAnalyzer windows) to force a SQL block to begin. I could then KILL the SPID when I was done testing.

Do any of you know of something like this? Is this so easy, no one would ever post it on the web Smile | :)

. If so, please let me know how I should go about this. If it's possible to create blocks without altering data, that'd be best.

Thanks!
Justin

merging two tables or two datasets

dansoft2-Feb-06 4:23

dansoft

2-Feb-06 4:23

Hi,

How can merge two tables? the select command for the 2 tables are as follows

select groupid, staff.name[created] from groups,staff where groups.created=staff.staffid

groupid created
------ --------
1 A
2 B

select groupid,staff.name[modified] from groups,staff where groups.modified=staff.staffid

groupid modified
------- --------
1 C
2 D

i have to merge these two output as

groupid created modified
------- ------- --------
1 A C
2 B D

please send me the sql query or please tell me how can i merge these using the dataset or any other solution. I have to implement this in my final year project.

Thanks in advance. Smile | :)

Re: merging two tables or two datasets

Chris Meech2-Feb-06 7:54

Chris Meech

2-Feb-06 7:54

select groupid
      ,staff1.name created
      ,staff2.name modified 
  from groups
      ,staff staff1
      ,staff staff2
 where groups.created = staff1.staffid
   and groups.modified = staff2.staffid
 order by groupid;

Chris Meech

I am Canadian. [heard in a local bar]

When I want privacy, I'll close the bathroom door. [Stan Shannon]

BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]

Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]

Re: merging two tables or two datasets

dansoft2-Feb-06 15:49

dansoft

2-Feb-06 15:49

Chris Meech wrote:
select groupid ,staff1.name created ,staff2.name modified from groups ,staff staff1 ,staff staff2 where groups.created = staff1.staffid and groups.modified = staff2.staffid order by groupid;

Hi, Chris Meech

Your query is working fine.

Thanks. Rose | [Rose]

Connection Pooling ADo.Net

faviochilo2-Feb-06 3:21

faviochilo

2-Feb-06 3:21

Hi friends:

How i can specify the max and min size of a connection pooling in a OldbConnection ?????

I hope you can helpme

Thanks in advance

Wink | ;)

Re: Connection Pooling ADo.Net

Le centriste2-Feb-06 6:52

Le centriste

2-Feb-06 6:52

Documentation says that connection pooling is handled by provider for OleDbConnection. If you are using SQL Server, you can use SqlConnection class, and use the Pooling, Max/Min Pool Size entries in the connection string.

--------

"I say no to drugs, but they don't listen."
- Marilyn Manson

Add JPEG In MySQL5.0 Table via VB6.0

jobje2-Feb-06 1:56

jobje

2-Feb-06 1:56

Hi, I'am trying to save a jpeg image from a file via vb6.0 ADODB to MySQL 5.0 Table i've named the datafield 'Photo ' and the datatype BLOB. If i do this Vb gives me een error message or no message at all.
The picture will not be saved.

Can anyone help me????please Confused | :confused:

Greetz Job

Is SELECT INTO optimal to copy data?

Salil Khedkar2-Feb-06 1:54

Salil Khedkar

2-Feb-06 1:54

I am writing a stored proc. Currently doing a "SELECT INTO" to copy data from one table to a new one. But eventually
a. I want to do it most optimally as the data is gonna be huge
b. Also it should be able to insert data in an existing table.

What SQL stmt/function should I use? D'Oh! | :doh:

Salil Khedkar [^]

Re: Is SELECT INTO optimal to copy data?

Mike Dimmick2-Feb-06 2:32

Mike Dimmick

2-Feb-06 2:32

INSERT INTO [table]
SELECT [col1], [col2], [col3], ...
FROM ...

Stability. What an interesting concept. -- Chris Maunder

-- modified at 8:32 Thursday 2nd February, 2006

Help!!Compacting Database MSDE/SQL

jfk_lili1-Feb-06 22:35

jfk_lili

1-Feb-06 22:35

Hi Guys,

I have not known so much about the SQL Server and MSDE, so just rush to here to get help.

Can any one tell me When records are deleted, is the space immediately compacted or when is it compacted?
and Is the compacting configurable? How to set it up? Since I am using the SQL buddy

For large number of records deleted (eg. Archival), is the database compacted immediately!

Many thanks in advance! I am really appreciate your help.

Re: Help!!Compacting Database MSDE/SQL

Mike Dimmick2-Feb-06 2:38

Mike Dimmick

2-Feb-06 2:38

SQL Server does not generally compact free space. To do so, use the Shrink feature (in T-SQL, DBCC SHRINKDATABASE).

To have this occur automatically, set the 'Auto Shrink' option. You can do this in T-SQL with ALTER DATABASE db SET AUTO_SHRINK ON. MSDE has this option turned on by default.

If Auto Shrink is set, the shrinking operation occurs when there is more than 25% free space in the file. The resulting file will have 25% space free or be the size it was when originally created, if that is larger.

Stability. What an interesting concept. -- Chris Maunder

Re: Help!!Compacting Database MSDE/SQL

jfk_lili2-Feb-06 17:02

jfk_lili

2-Feb-06 17:02

Hi Mike,

Thanks very much for your reply. So the compacting is a default option which already be turned on in MSDE? How about SQL Server, the option is also turned on by default?

Is there any way I can configure this option so that it can be performed when there is 10% free space?

Many thanks for your help!!

Problems with Backup/Restore of SQL server database

nicsquare1-Feb-06 20:44

nicsquare

1-Feb-06 20:44

I have a problem in restoring sql server database in asp.net
I have first written code for full backup and differential backup of database, it is working properly.

For full backup code is :
-----------------
-----------------
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaName = Server.MapPath("tempbakupFull.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Full" & Now
-------------------
------------------

For Differential backup :
objBackUp.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Differential
objBackUp.Database = "tempbakup"
objBackUp.Devices = ""
objBackUp.Files = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaName = Server.MapPath("tempbakupDiff.zip")
objBackUp.MediaDescription = "TempBakup Database Backup Differential" & Now

But when I used wrote following code for restore databse, I got problem

"The volume on device 'c:\inetpub\wwwroot\TestApp\tempbakupfull.zip' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set."

CODE is

oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
oRestore.Database = "TempBakUp"
oRestore.Files = Server.MapPath("tempbakupfull.zip,tempbakupdiff.zip")
oRestore.FileNumber = 1
oRestore.ReplaceDatabase = True
oRestore.LastRestore = False
oRestore.SQLRestore(srv)

oRestore.FileNumber = 2
oRestore.LastRestore = true
oRestore.SQLRestore(srv)

date pblm in mssql server

Vipin.d1-Feb-06 17:27

Vipin.d

1-Feb-06 17:27

hi all,
i want to select all the jobcode from jobmaster

my query is
SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast
it will work fine
it select entries like

test 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107

but when use the following querry,

SELECT txtjcode,convert(smalldatetime,dtlastdate)as last ,getdate()as tday FROM jobmast WHERE convert(smalldatetime,dtlastdate)>= getdate()

it will select only this data
US002 2006-02-10 00:00:00 2006-02-01 09:51:05.107

and didnt select

US001 2006-01-25 00:00:00 2006-02-01 09:51:05.107

actually my query select both
why happens this so?
any soln to this

"i want to select all data where last date >= today date"
plz help urgent Frown | :(

CrazySanker

All I ever wanted is what others have....

Re: date pblm in mssql server

Rana Muhammad Javed Khan1-Feb-06 20:00

Rana Muhammad Javed Khan

1-Feb-06 20:00

One solution is that u should only save DATE portion in the datetime field u can send it as
Format(dtPicker.Value,"mm-dd-yyyy")
then ur query will work fine.

Regards,

Javed

Re: date pblm in mssql server

Colin Angus Mackay1-Feb-06 23:26

Colin Angus Mackay

1-Feb-06 23:26

What type is dtlastdate and why are you converting it? Shouldn't it already be in a datatime (or smalldatetime) format already?

crazysanker wrote:
WHERE convert(smalldatetime,dtlastdate)>= getdate()

It didn't select the row with dtlastdate as 2006-01-25 because that is before now. You asked to select rows with a dtlastdate after now.

>= Greater than or equal to: WHERE dtlastdate is greater than or equal to getdate()

The query is working correctly.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Copy diagram from on db to another on same db

bubberz1-Feb-06 12:02

bubberz

1-Feb-06 12:02

I know you can view the diagram icon, highlight it and click Cut or Copy, but if I go to another database with the same exact schema, how do I "paste" that diagram?

Thanks!

Re: Copy diagram from on db to another on same db

bubberz2-Feb-06 3:37

bubberz

2-Feb-06 3:37

Here's how:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125

generating database

D.N.1-Feb-06 4:09

D.N.

1-Feb-06 4:09

I am new in database, somebody has sent me a small database via mail its a file, how can i generate a database using it.

i just know that i need to create a database through Enterprize Manager but how to fill it using some file

please guide me step by step

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