multiple records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple records

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-17 : 04:03:55

Dear ALL,
I have two tables with named Reports and Backup_Reports with same fields. Id(numeric), MobileNumber(varchar), SmsBody(varchar),date(datetime),Status(char).
Here what I want is, if I am searching some records by entering the mobile number then the sql query first will search on the first table Reports then it will show the related records and again the query will search on the second table Backup_Reports then will show related records from this table also.
What I mean is, if I am searching some records by mobilenumber first will search on the Reports and then will go to second table Backup_Reports and will show all the related records together.
How can I handle this?

regards
Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 04:15:56

[code]SELECT *
FROM
(
SELECT Id, MobileNumber, SmsBody,date,Status
FROM Reports
UNION
SELECT Id, MobileNumber, SmsBody,date,Status
FROM Backup_Reports)t
WHERE t.MobileNumber=@MobileNumber
[/code]

Where @MobileNumber is the moble number you're looking for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-17 : 04:49:49

Visakh, no need to UNION two tables (which is a costcome operation and then afterwards check for mobilenumber.
Do that directly and UNION will perform much faster.

SELECT *
FROM
(
SELECT Id, MobileNumber, SmsBody,date,Status
FROM Reports
WHERE MobileNumber = @MobileNumber

UNION

SELECT Id, MobileNumber, SmsBody, date, Status
FROM Backup_Reports
WHERE MobileNumber = @MobileNumber

E 12°55'05.25"
N 56°04'39.16"

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-17 : 04:52:01

Thanks for your replay Mr.visakh16,
The query that u have given is ok. Now I have one more think, each week we are taking the backup of our Reports table. So each week one table will increase with name Backup1_Reports (for first week), Backup2_Reports (for second week), Backup3_Reports (for third week) etc. It will increase after passing each week. Now how to write a general query to fetch records from all these tables. I mean here the name of table is changing each week.

regards
Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 15:09:35

quote:
Originally posted by shajimanjeri

Thanks for your replay Mr.visakh16,
The query that u have given is ok. Now I have one more think, each week we are taking the backup of our Reports table. So each week one table will increase with name Backup1_Reports (for first week), Backup2_Reports (for second week), Backup3_Reports (for third week) etc. It will increase after passing each week. Now how to write a general query to fetch records from all these tables. I mean here the name of table is changing each week.

regards
Shaji

You might have use a dynamic sql for this.Any special reason why you are using a seperate table to store each week data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 15:10:12

quote:
Originally posted by Peso

Visakh, no need to UNION two tables (which is a costcome operation and then afterwards check for mobilenumber.
Do that directly and UNION will perform much faster.

SELECT *
FROM
(
SELECT Id, MobileNumber, SmsBody,date,Status
FROM Reports
WHERE MobileNumber = @MobileNumber

UNION

SELECT Id, MobileNumber, SmsBody, date, Status
FROM Backup_Reports
WHERE MobileNumber = @MobileNumber

E 12°55'05.25"
N 56°04'39.16"

Ok Peso. Thanks for the information

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-19 : 03:51:11

Dear Mr. visakh16,

I am creating multiple tables on each week is because everyday we are storing more than 1000000 records in our db. so for one week it will be 7*1000000. While displaying these much of records in our web page it shows script timeout. I put Server.ScriptTimeout=2000000 (maximum) but still the timeout shows while fetching these records.

regards
Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 04:01:49

quote:
Originally posted by shajimanjeri

Dear Mr. visakh16,

I am creating multiple tables on each week is because everyday we are storing more than 1000000 records in our db. so for one week it will be 7*1000000. While displaying these much of records in our web page it shows script timeout. I put Server.ScriptTimeout=2000000 (maximum) but still the timeout shows while fetching these records.

regards
Shaji

What you could do is to put all information except current weeks to a archive tabl each week. May be schedule a job to call a sp which does this once a week. I dont think you'll be interested in all records before current week always.Whenever you want old records alone, query against the archive table.

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-19 : 05:53:13

This was my first method that I did, but then its shows timeout error on my web page. Then I tried the second method (creating multiple tables in each week) this works fine. with out any timeout problem on my web page. I am using ASP to code my web page. Can you explain to me how to avoid timeout error on my web page while accessing huge data from sql server? Is there any limit for sql to execute a select query?

regards
Shaji

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 05:54:54

Add a proper index.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources