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.
| Author |
Topic |
|
IMGrumpy
Starting Member
11 Posts |
 Posted - 2010-11-30 : 13:12:39
|
Greetings:I can't seem to figure out how to do what I thought SHOULD be a pretty simple thing.A need a built-in procedure to create a table. But it is the table name that is passed to the procedure. I have a requirement in which I must be able to create many tables, each with the same format, but with different names. My code to create the procedure is below:
CREATE PROCEDURE FieldData.CreateTable( @TableName VARCHAR(50) )ASBEGIN DECLARE @WasCreated BIT -- Nifty trick to create a new, empty, table from a previously defined existing table SELECT * INTO <WHAT GOES HERE?> FROM Template.PositionTable WHERE 1=2 ENDGO
In the part that designates the name of the new table, I have inserted the <WHAT GOES HERE?> place-holder.I have tried everything I can image and nothing is working. Either the code will not execute, complaining about the Table Name specifier OR it will execute but when I run the code, I find I have a new table called: "@TableName" or "dbo.@TableName" - anything other than the name of the table that I passed to the procedure.Can anyone help me out with this?Thank you,Mark |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-11-30 : 13:37:36
|
| You are looking at using dynamic SQL:[CODE]declare @sql nvarchar[4000]set @sql = 'SELECT * INTO ' + @TableName + 'FROM Template.PositionTable WHERE 1=2'exec sp_ExecuteSQL @sql[/CODE]=======================================No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987) |
 |
|
|
IMGrumpy
Starting Member
11 Posts |
Posted - 2010-11-30 : 14:15:42
|
| Hey, thank you very much. It worked.I must say though, I find it surprising that there is not a more elegant way. I had turned to stored procedures because I was becoming weary of dynamically constructing SQL statements from within my C++ and C# code...But at that point, I'll take a solution that gets the job done.Regards,Mark |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-30 : 14:56:15
|
quote:
I must say though, I find it surprising that there is not a more elegant way
Well, the more elegant way is to NOT create tables on the fly, especially if they have identical structure. Define that structure one time and insert all data into it. This is a key tenet of relational database design. |
|
|
|
IMGrumpy
Starting Member
11 Posts |
Posted - 2010-11-30 : 16:03:15
|
quote:
Well, the more elegant way is to NOT create tables on the fly, especially if they have identical structure. Define that structure one time and insert all data into it. This is a key tenet of relational database design.
The problem is that a single table will hold GPS data records for many different devices and after some time, we are looking at possibly millions of records in a single table if we combine them all. We felt that it might be best to keep separate tables for separate devices and employ a cross reference table to determine which table maps to which device. If you know of a way to manage large numbers of records and still make searching efficent, I'd like to hear. I am a novice at this and I am open to suggestions. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-30 : 16:15:59
|
| Millions of rows are not a problem for SQL Server as long as the data is properly indexed. If you need to differentiate devices, add a "Device" or "DeviceID" column that logs which device recorded those GPS coordinates. |
|
|
|
IMGrumpy
Starting Member
11 Posts |
Posted - 2010-12-01 : 09:21:53
|
| Thank you, I will take your advice very seriously and try to convince my colleagues (also novices)... |
|
|
|
|
|
|
|
|
Use Expression to Create Table