Using A Variable To Create Temp Table

Mar 3, 2003

Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.

Thanks,
Dianne

View 2 Replies


ADVERTISEMENT

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

Using A Temp Table And Variable With Gridview

Jun 16, 2008

Hi all, Im trying to use a temporary table along with a gridview.  But i keep getting an issue that says that I need to declare the variable name.  
 Right now, I have it set up that I have a permanent table in the database that is serving as my temporary table, however Im running into issues if multiple users access at once... So I would like to dynamically create a table and use that for my temporary table and then drop the table when the user is done with it...
 heres my code so far..Private Sub AddRecords(ByVal prodins As String, ByVal releaseins As String, ByVal opsysins As String, ByVal currelins As String, ByVal schemains As String, ByVal commentins As String, ByVal freeformins As String)
Dim tablevar As String = lblRequestor.TextDim tablename As String = "##" + tablevar Dim myConnection As New SqlClient.SqlConnection
Dim query As String = "CREATE TABLE " + tablename + "(UniqueID int Identity(1,1), Product varchar(50), OperatingSystem varchar(50), CurrentRelease varchar(50), Release varchar(50), SchemaSize varchar(50), Comments varchar(50), TempRelease varchar(50), FreeFormFlag varchar(50), sourcereleasedir varchar(125))"
Dim query2 As String = "INSERT INTO " + tablename + "(Product, Release, OperatingSystem, CurrentRelease, SchemaSize, Comments, TempRelease, FreeFormFlag) VALUES ('" & prodins & "','" & releaseins & "', '" & opsysins & "', '" & currelins & "', '" & schemains & "', '" & commentins & "', '" & currelins & "', '" & freeformins & "')"Dim myCommand As SqlClient.SqlCommandmyConnection = New SqlClient.SqlConnection("Data Source=mydatasource")
myConnection.Open()myCommand = New SqlClient.SqlCommand(query, myConnection)
myCommand.ExecuteNonQuery()myCommand = New SqlClient.SqlCommand(query2, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
 
And here is my query for the gridview....
 SelectCommand="SELECT * FROM @tablename where FreeFormFlag <> 'Y'"
and
<SelectParameters>
<asp:ControlParameter ControlID="tablename" Name="tablename" PropertyName="Text" DefaultValue="##default" />
</SelectParameters>
so @tablename is set to the value of a textbox i have hidden which the textbox gets the username of the user and adds ## to it..so if john doe comes on... the texbox reads ##jdoe... and thats what im trying to name the temp table.    SO the syntax is all there.... But i keep getting an error saying i need to declare @tablename... which is puzzling to me.. I tried doing
declare @tablename varchar (50)
select.....etc.  
 
but then it brings up an error saying... @tablename is already declared, each variable must be unique..., You must declare @tablename...   It tells me that its already declared then tells me i must declare it???  it makes no sense..
 
Anyone have any ideas?
Thanks,

View 4 Replies View Related

Integration Services :: How To Use Temp Table / Variable

Aug 8, 2014

It's SQL 2008 R2. I need to bring data from Oracle using .Net Providers/ODBC Data Provider to MS SQL table converting Oracle UTC dates to PST.  The source connection type cannot be changed as it's given. For the Destination I'm using the OLE DB.

As the truncate all and load could take time I'm trying to use a temp table or a variable to use it further with t-sql merge or not exists to bring/add the only new records to the destination table.

I'm trying different scenarios that is all failed.

Scenario A:

1. In DTF after OLE DB Source I'm using the Derived Colum to convert dates. It's working well.

2. Then use Recordset Destination with an object variable User::obj_TableACD. It's also working well.

3. Then I created a string variable with a simple query that I could modify later "select * from " + (DT_WSTR,10)@[User::obj_TableACD] trying to get data from the recordset object variable but it's not working.

Scenario B:

1. Created a store procedure to create a temp table.

2. Created a string variable to execute SP str_CreateTempTable: "EXEC dbo.TempTable". It's working well with the SQL Task with SQLSourceType as Variable.

3. Then how to populate the temp table from the Oracle source to bring data into the Destination?

View 6 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

The Age Old Argument Of Temp Table Vs Table Variable

Jan 26, 2007

Hi All,Hope someone can help me...Im trying to highlight the advantages of using table variables asapposed to temp tables within single scope.My manager seems to believe that table variables are not advantageousbecause they reside in memory.He also seems to believe that temp tables do not use memory...Does anyone know how SQL server could read data from a temp tablewithout passing the data contained therein through memory???Is this a valid advantage/disadvantage of table variables VS temptables?

View 2 Replies View Related

Table Variable Versus #Temp Table

Jan 17, 2008

In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1

It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.

Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?

Thanks in advance for any help.

View 4 Replies View Related

I Can't Create A Temp Table

Jul 20, 2005

Hi all!I have a problem with a temp table.I start creating my table:bdsqlado.execute ("CREATE TABLE #MyTable ...")There is no error. The sql string has been tested and when it'sexecuted in the sql query analyzer it really creates the table.After creating the table, I execute an insert statement:bdsqlado.execute ("INSERT INTO #MyTable VALUES(...) "It returns an error like this: "Invalid Object Name #MyTable"I don't understand what's wrong. If I execute both sql sentences inthe SQL Query Analyzer it works perfectly.I use the same connection to execute both statements and I don't closeit before the INSERT is executed.I think it may be something related to dynamic properties of theconnection, but I'm not sure. It's just an idea.Please I need help.Thanks a lot,

View 1 Replies View Related

SQL Server 2008 :: Temp Table - Must Declare The Scalar Variable

Mar 25, 2015

I can create a temp table like this, and immediately read the data:

declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?

Sep 17, 2007

In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View 1 Replies View Related

CREATE A Temp Table Via EXEC (@SQL)

Jan 23, 2006

I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?

View 4 Replies View Related

Create Temp Table Instead Of View?

Aug 23, 2013

I have a view which works fine but I cannot display the data in the Report tool because its

CCSID is HEX. If I could create it to temp table

I think then there would be an easy way to get around this problem.

This is the code:

CREATE VIEW astlib.acbalmpk AS (
(SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1,
COALESCE(IRLOC2,'')
as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,
'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as
IELOC3, '' as IERIDC, '' as IEWHS#

[Code] ....

View 2 Replies View Related

Newbie How To Create Temp Table And Populate

May 8, 2006

Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript
Thanks
M

View 4 Replies View Related

How Can I Create A Temp Table On A Linked Server?

Apr 11, 2007

Hello

I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:

create table #myRemoteTempTable

insert into #myRemoteTempTable
select * from myLocalTable

update myRemoteTable
set
Value=#myRemoteTempTable.Value
from myRemoteTable
inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID

View 6 Replies View Related

Find The Datatypes That Where Used To Create A Temp Table

Feb 18, 2008

Is there a way to find out what the datatypes of a temp table are?

Example:

select cust_code, cust_name, cust_state
into #customers
where cust_state = 'TX'


I would like to know what datatypes SQL used when creating #customers.

Thank you for all the help.

View 2 Replies View Related

Create Temp Table/loop Through Records

Jun 19, 2008

Hi all

I'm new to sql and could do with some help resolving this issue.

My problem is as follows,

I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.

The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:

In a simple form it would look like this:

LevelRef: BomA

1component A
1component B
1Bom D
1component C


What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level

Which would in its simplest form look something like this

LevelRef: BomA

1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C

I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.

also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.


Bill Shankley

View 4 Replies View Related

Create A View Or Temp Table From 2 Tables

Jan 4, 2008



I have 2 tables:


Customer Table: ID, OrderID (composite key)

100, 1
100, 2
200, 3
200, 1
Order Table: OrderID, Detail

1, Orange
2, Apple
3, Pineaple


Assuming each customer always orders 2 items. I need to create a SQL query that shows as following (a view or a temp table is OK). How do I do that?


CustomerID, Order Detail1, Order Detail2

100, Orange, Apple
200, Pineaple, Orange



View 10 Replies View Related

Compare Data-type Xml In A Temp/variable/physical Table In MSSQL 2000

May 22, 2008

Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?

I tried this works in MSSQL 2005,



Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2


but not MSSQL 2000.

Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.


Is this true (from BOL)?




Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.

View 1 Replies View Related

How Do I Create A Temp Table As The Beginning Of A Process Flow?

Dec 29, 2006

Hi
Folx,
I
am new to SQL Server and I am struggling.

Versions:
Microsoft
SQL Server Integration Services Designer Version
9.00.1399.00





Microsoft
SQL Server Management Studio 9.00.1399.00













I
would like to
01.
create a temp table
02.
load the temp table from a flat file
03.
insert into a destination table the rows from the temp table where NOT EXIST the
primary key of the destination table.



ISSUES:



Flat
File Source will not accept that a resource will be available that does not yet
exist (the temp table)



I
set the Flat File Source to €œIgnore Failure€? and ran the package. It ran with
warnings but did not insert the new rows.



The
€œIgnore Duplicates€? radio button is €œgrayed out€? because the index is clustered



Now
I could work around this thing by keeping a table just for purposes of this
process flow. I am opposed to that philosophically and would prefer to do this
in the way that I consider appropriate€¦is there a solution?



Thanks,
Bill

View 4 Replies View Related

Table Variable Vs Temp Table

Oct 15, 2004

I have read alot of articles saying that we should use Table variable for small volume of data, Temp table for large volume of table.

However, how to determine small and large??? Willing for your comment and answer..

View 1 Replies View Related

Table Variable V/s Temp Table

Sep 20, 2007



Hello,
Which is better? Using a table variable or a temporary table?

Mannu.

View 3 Replies View Related

When To Use Temp Table And Table Variable

Nov 17, 2015

in order to store huge data, should i use temp table or table variable(performance wise) and why?

View 2 Replies View Related

Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?

Dec 28, 2006

Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
 My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
 Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
 Any help gratefully appreciated.
Thanks.
John

View 2 Replies View Related

Create Temp Table To Store Data From Database(mssql)

Jun 26, 2007

I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?

View 2 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Transact SQL :: Create Temp Table That Persists While A Front End App Is Open

Sep 30, 2015

I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.

USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]

[code]...

View 10 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?

Jun 9, 2014

I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below

CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]

for i want to generate table for the above feilds with datatype

View 5 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 Replies View Related

How To Create Index On Table Variable (Table Don't Have Primary Key)

Feb 26, 2008



Hi all,


my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...


DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)

I tryed all below senarios...it is giving error...


--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))


View 3 Replies View Related

Help!! Create Table From Variable !!!

Jun 16, 2008

I am passing a variable to a stored procedure using
db.ExecuteNonQuery("dbo.CreateTable", @symbol); < C# CODE >
the variable shows up fine but the stored procedure does not create the table...
I have tried everything... here are two versions of code that do not work...

using Dynamic Sql....

CREATE PROCEDURE dbo.CreateTable
@symbol nvarchar(10)
AS
DECLARE @Sql varchar
SELECT @SQL = 'Create Table [dbo].['+ @symbol +'](Symbol float'
SELECT @SQL = @SQL + '
, [Date] datetime
, [Open] float
, High float
, [Low] float
, [Close] float
, Volume integer)'
EXEC (@SQL)

this does nothing

And this one...(the longer version)

CREATE PROCEDURE dbo.CreateTable
(
@Symbol as varchar (10)
)
AS
DECLARE @SQL varchar(2000)
SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@Symbol]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@Symbol]
CREATE TABLE [dbo].[" + @Symbol + "] (
[Ticker] [varchar](10) Null,
[Date] [date] Null,
[Open] [float] NULL ,
[High] [float] NULL ,
[Low] [float] NOT NULL ,
[Close] [float] NULL ,
[Volume] [float] NULL ,
) ON [PRIMARY]"
EXEC(@SQL)

GIVES ME A 'The identifier that starts with... is too long, maximum length is 128'

View 2 Replies View Related

Create Table With Variable Name

Jun 26, 2006

This should be simple, but...

I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....

CREATE PROCEDURE foo

-- Add the parameters for the stored procedure here

@TableName char = null

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE @TableName (

[HRMONTH] [int] NULL,

[HRYEAR] [int] NULL

) ON [PRIMARY]



But no combination of names '@'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...

Thanks,

Tom

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved