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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 useTable name as a variable
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/07/2006 :  02:04:52  Show Profile
Hi
I want to use Table name as variable like this

declare @tablename varchar(10)

select * from @tablename

how it will done

Ranjeet Kumar Singh

Edited by - ranjeetsingh_6 on 09/07/2006 02:16:14

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 09/07/2006 :  02:22:58  Show Profile
use Dynamic SQL

declare @sql varchar(8000)
select @sql = 'select * from ' + @tablename

exec (@sql)



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  02:33:22  Show Profile  Visit SwePeso's Homepage
Why, Oh why, do you want to do that?

The answer is here at http://www.sommarskog.se/dynamic_sql.html


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/07/2006 :  04:23:05  Show Profile
Hi

We are working on a project VEHICLE monitoring in which we find a VEHICLE position according to VEHICLE moveing.we find place of a VEHICLE according to Latitude and Longitude.Our problem is if nomber of VEHICLE increase then master table(which have place according to Latitude and Longitude of VEHICLE)data increase more .and to find place for a particular Latitude and Longitude of a VEHICLE we match this Latitude and Longitude from master table which take more time because master table have records of total vechile .

so we thought that we create different table for a different VEHICLE
and when we need to find a VEHICLE position then we will search a particular table of this VEHICLE and by latitude and longitude we will find Place.but by concept maintanance of table will tough because we have one table for one VEHICLE

suggest me our concept is how much right.


Ranjeet Kumar Singh
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/07/2006 :  04:29:41  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
quote:
Originally posted by ranjeetsingh_6

Hi

We are working on a project VEHICLE monitoring in which we find a VEHICLE position according to VEHICLE moveing.we find place of a VEHICLE according to Latitude and Longitude.Our problem is if nomber of VEHICLE increase then master table(which have place according to Latitude and Longitude of VEHICLE)data increase more .and to find place for a particular Latitude and Longitude of a VEHICLE we match this Latitude and Longitude from master table which take more time because master table have records of total vechile .

so we thought that we create different table for a different VEHICLE
and when we need to find a VEHICLE position then we will search a particular table of this VEHICLE and by latitude and longitude we will find Place.but by concept maintanance of table will tough because we have one table for one VEHICLE

suggest me our concept is how much right.


Ranjeet Kumar Singh



Instead of maintaining one table per Vehicle, why don't you partition your existing master table into multiple smaller tables and use partitioned view ?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  04:31:06  Show Profile  Visit SwePeso's Homepage
How many records are there in Master Table?
If there are less than 100 million rows, there is no need to "partition" the records.
Just make a column named VehicleID and you're set!



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  04:31:40  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
what query you are using for retriving the values from the master table?

using seperate for each vehicle will be Ad-hoc when the number of the vehicle increase... its better to keep that out of option..

Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  04:55:07  Show Profile  Visit SwePeso's Homepage
You can also make a "history" table.

Keep one Master table, with all data for last month or so. When data gets older, move the data to a history table.

Or only keep a week, or one day only.


Peter Larsson
Helsingborg, Sweden

EDIT: Last sentence

Edited by - SwePeso on 09/07/2006 05:01:46
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/07/2006 :  04:55:34  Show Profile
Hi
suppose we have 100 VEHICLE and its data into master table(its ID,latitude ,longitude,place) and suppose one VEHICLE(its id is V100) among 100 VEHICLE moveing in London at latitude=23.9876654327 and longitude=72.6543783211 and then i want to find the nearest place
of this VEHICLE according to its latitude and longitude to searching
master table which kept place for a latitude and longitude.

if i partition master table then its become like concept to make different table for a diff VEHICLE.

there are not fix how much will record in master table.its depends upon VEHICLE moveing records.

Ranjeet Kumar Singh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  05:00:18  Show Profile  Visit SwePeso's Homepage
No, you will only have TWO tables. And they can be UNIONed in a view very easy with no maintenance, because these two tables are all you got.

How many records do you have today? How many records do you expect per VEHICLE and DAY? 50 readings per vehicle and day? That's 5000 records per day. You will at least have a go for 20000 days, which is approx 54 years, keeping the record count under 100 million.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 05:02:27
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  05:04:50  Show Profile  Visit SwePeso's Homepage
If I interpreted you correctly, you lend some vehicles and want to keep track of them by using GPS information. Keep only the current lend for each vehicle in "master" table, other lendings in history table.

1) Is there a need to search previous readings in the same table?
2) What is the use for "nearest place"? Assistance of some kind?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 05:06:50
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/07/2006 :  05:10:29  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
Also after partitioning you don't have to bother about whether to search in current table or history table. You just have to write view which will be single BIG virtual table, and let SQL Server decide from which table to bring data based on your WHERE condition !!

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  05:26:48  Show Profile  Visit SwePeso's Homepage
Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 05:27:47
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  05:48:57  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
quote:
Originally posted by Peso

Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.


Peter Larsson
Helsingborg, Sweden



I hope this Boss doesnt see this post else he may start receiving deduction for next 2 month..

Chirag
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/07/2006 :  05:57:43  Show Profile

Hi
yes i lend some vehicles and want to keep track of them by using GPS information. Keep only the current lend for each vehicle in "master" table, other lendings in history table.

1) yes we are searching previous 60 days records in the same table?
2)"nearest place" means we are find most nearest place of a latitude and longitude.Because it is not possible that we kept all places for every latitide and longitude.

i am sending table structure and again focous on problem

VEHICLETRACK Table Contain current position of Vehicle (By Tables's Latitude and longitude we will Find place)

CREATE TABLE [VEHICLETRACK] (
[Jrid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deviceid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lat] [float] NULL ,
[Lon] [float] NULL ,
[Timerecorded] [datetime] NULL ,
[id] [decimal](10, 0) NOT NULL ,
[status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[other1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[other2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Speed] [float] NULL ,
[Distance] [float] NULL ,
[DirLat] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DirLon] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userid] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


------------------------------------------------------------

Map_Places_India Table Contain main places and its latitude and longitude.

CREATE TABLE [Map_Places_India] (
[ID] [decimal](18, 0) NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lat] [float] NULL ,
[Lon] [float] NULL
) ON [PRIMARY]
GO


-------------------------------
when any latitude and longitude will insert into VEHICLETRACK Table then we find nearest place of this latitude and longitude (I mentain nearest place it means we have not stored all places in Map_Places_India Table for each inserted latitude and longitude because it is not possible) .

My goal is to find place for inserted latitude and longitude and
there are 100 VEHICLE are using this Software.

Suppose two place have distance 1000 meter and one VEHICLE change its movement 10 times between these two places and its will return
10 latitude and langitude,each among ten latitude match 2000 records(from Map_Places_India Table )to find its nearest place.

This is example of only one VEHICLE which move between 1000 meter and stop 10 times suppose its cover 500 Km. and stop 300 times
then each 300 latitude search its place.Similarly it may for multiple VEHICLE.

Our main moto is performance and maintance






Ranjeet Kumar Singh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/07/2006 :  06:08:30  Show Profile  Visit SwePeso's Homepage
I have a suggestion.

1) Store the last 60 days of information in a "Master" table (or as long as the current lend period is), for each vehicle.
1.5) When lending period is over for any vehicle, move the data to a history table.
2) Create a view that returns LAST (in time) position of every vehicle.
3) Use pythagoras theorem to get "nearest" place for very vehicle in the view.

Does it sound simple enough?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 06:56:13
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/07/2006 :  06:52:26  Show Profile
Hi
Thanks I think it will better implementation.I am working on it.



Ranjeet Kumar Singh
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/07/2006 :  07:16:52  Show Profile  Visit jsmith8858's Homepage
Add indexes and a PK to your table and be sure that your sql is optimized before doing any other things to attempt to increase performance .

- Jeff

Edited by - jsmith8858 on 09/07/2006 08:47:06
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/07/2006 :  07:21:31  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
quote:
Originally posted by chiragkhabaria

quote:
Originally posted by Peso

Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.


Peter Larsson
Helsingborg, Sweden



I hope this Boss doesnt see this post else he may start receiving deduction for next 2 month..

Chirag




Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  07:34:04  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
quote:
Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!


Are you working for SQL Team..

Chirag
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/07/2006 :  08:06:32  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
quote:
Originally posted by chiragkhabaria

quote:
Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!


Are you working for SQL Team..

Chirag



If that were the case, I would not be posting here !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000