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
 Old Forums
 CLOSED - General SQL Server
 useTable name as a variable

Author  Topic 

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-07 : 02:04:52
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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-07 : 02:22:58
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

30421 Posts

Posted - 2006-09-07 : 02:33:22
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

125 Posts

Posted - 2006-09-07 : 04:23:05
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 04:29:41
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

30421 Posts

Posted - 2006-09-07 : 04:31:06
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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 04:31:40
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

30421 Posts

Posted - 2006-09-07 : 04:55:07
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
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-07 : 04:55:34
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

30421 Posts

Posted - 2006-09-07 : 05:00:18
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:04:50
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
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 05:10:29
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

30421 Posts

Posted - 2006-09-07 : 05:26:48
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
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 05:48:57
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

125 Posts

Posted - 2006-09-07 : 05:57:43

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

30421 Posts

Posted - 2006-09-07 : 06:08:30
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
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-07 : 06:52:26
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

7423 Posts

Posted - 2006-09-07 : 07:16:52
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
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 07:21:31
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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 07:34:04
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 08:06:32
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
    Next Page

- Advertisement -