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


ADVERTISEMENT

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

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

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 View Related

SQL 2012 :: Create A Table Using Value Of A Variable

May 14, 2014

create a table using the value of a variable

example:
create uspCreateTable
@ NameTable varchar (10)
the
begin
create table @ NameTable (
id int,
Name varchar (20))
end

the intention is to create a procedure that creates tables changing only the name.

View 9 Replies View Related

Table Name In Variable - Create Statement

Jul 20, 2005

Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric

View 3 Replies View Related

Create A Variable Type TABLE

Aug 29, 2007


I€™ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. . The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TESTE]
ON [dbo].[TABTESTE]
FOR INSERT
AS
DECLARE
@YearTable nvarchar(4),
@IdClient INT,
@MyTable TABLE
(
IdClient INT,
Situ NVARCHAR(50)
)
BEGIN
SET NOCOUNT ON;
SELECT @YearTable = SITUACAO, @IdClient = IdClient FROM INSERTED
SET @MyTable = 'TABLE' & @YearTable
UPDATE @MyTable
SET
Situ = 'X'
WHERE IdClient = @IdClient
END
GO
Erros:
Msg 156, Level 15, State 1, Procedure TESTE, Line 9
Incorrect syntax near the keyword 'TABLE'.
Msg 137, Level 15, State 1, Procedure TESTE, Line 17
Must declare the scalar variable "@MyTable".
Msg 1087, Level 15, State 2, Procedure TESTE, Line 18
Must declare the table variable "@MyTable".

View 8 Replies View Related

Variable To Create A Table In A SQL Task

Nov 6, 2007

Hi

I need to create a Table using the SQL Task and a Variable as the Table Name

I am getting an Error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The SQL Statement that I am using is
Create Table ? (ColumnA char(5) Null)

The ? is the Value of the Variables

Is there a way of doing this

RegardsQ

View 4 Replies View Related

How To Create Table Names By Using Macro Variable? Thanks!

Oct 21, 2005

Greetings!I am now doing one type of analysis every month, and wanted to creattable names in a more efficient way.Here is what happens now, everytime I do the analysis, I will create atable called something like customer_20050930, and then update thetable by using several update steps. Then next month I will create atable called customer_20051031. Does anyone know if there is a betterway to do it? like using a macro variable for the month-end date? Noweverytime I have to change the table name in every single update step,which would cause errors if I forget to change one of them. By using amacro, I would only need to change it once.Thanks!

View 8 Replies View Related

How To Create Trigger Which CREAT TABLE From A Variable String?

Feb 23, 2006



I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.

Here is what I did but It needs to be corrected:

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

ALTER TRIGGER CreateTopicsTableTrigger

ON dbo.Forums

AFTER INSERT

AS

SET NOCOUNT OFF

DECLARE @myNewForum varchar

CAST(@@ROWCOUNT as varchar) /*Is it OK???*/

SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/

GO

CREATE Table @myNewForum /*Will this work some how???*/

( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,

DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )



::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

View 5 Replies View Related

Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login

Mar 3, 2014

I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.

I'm getting an 'incorrect syntax' error at the variable. For example ..

CREATE LOGIN @NAME WITH PASSWORD 'password'

I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.

View 3 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Dts - Can You Create A Variable Within A Dts

Nov 14, 2000

I have a dts set up to take info from our as/400 and load it onto into SQL 7.0 server. I would like to within my select that I'm using to load up the data calulate a variable or create a temp table with one row that has the current fiscal year. This fiscal year would then be used in my select to determine what data to bring in. But it doesn't seem like I can use a table on the server within my select statement on the destination portion of my dts.
This is what I would like to accomplish
select *
from iptsfil.ipwkhst ,swoolib.DSSCNTL
where
KYR#=select max(fiscyear) from fiscalyear(this is my temp table that I have already created)
AND KWK# = STRNUM
AND KCT#=1
AND TBLNME = 'WEEKHST'

the dts doesn't seem to like me using both file from the server and the 400 at the same time. Is there a way to do what I'm trying to accomplish

View 1 Replies View Related

Create Global Variable Like VB... Can It Be Done?

Feb 8, 2007

I just want to store the value of a parameter in a global variable that all my reports in the same project can use.

My goal is to create a dynamic query. For example:

Company Name: Widgets inc.

Divisions: Sales, Service, Tech, Accounting

I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.

This way, on the next report, my query's where statement is the global variable.



@GlobalVariable = parameter1.division.value

Select name, address, phone FROM Employee WHERE division = @GlobalVariable

I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.

Please let me know if anyone can help.

Thanks.

John

View 3 Replies View Related

How To Use Variable To Create Index

Sep 26, 2007



Hi ,

I would like to create index for a table and that index name must be random generated.

How to do this?


declare @value varchar(50)

set @value = rand()

set @value = @value + 'index-name'

create index @value on tablename(variables)

View 1 Replies View Related

How Do I Create A Variable In A View

Mar 17, 2006

Hello, We have a need to create a variable within a View. We tried declaring our variable like we would in a stored procedure, but that doesn't seem to work. What is the proper way to do this? Thank you. Kay

View 1 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

Can I Create The Variable With Scope --Project

Mar 19, 2008

can i create the variable with scope --Project

View 5 Replies View Related

How Can I Check And Create The Variable File Name?

May 15, 2008



Hi,

Everyday they are sending to our servers a file named like CC_20080501 . it is CC_YYYYMMDD
So I have to check it and move into the database table.
I am thinking to create standard database connection and name like CC_std
And When the file arrives to create something like CC_std (actually copying my daily file CC_YYYYMMDD to CC_std ?
BUT how can I do it?
any other way to do it?

thanks,
John

View 6 Replies View Related

Can I Create The Variable With Scope --Project

Mar 19, 2008



Hi All,

Could u plz tell me how to create a variable with scope project but not package(normally)

View 4 Replies View Related

Create Global Variable And Use It In Different Reports

Mar 10, 2008

I have some reports in the same project.
Is it possible to create a global variable that will be used in the different reports?
Thanks in advance.

View 5 Replies View Related

How To Create A Select Statement With An Increasement Variable?

Dec 19, 2003

Example:

Select icount + 1 as icount from table

or

Select counter() as icount from table

The above is wrong ...just a sample to show what I am trying to accomplish.

Is there a function in SQL statement? Thanks.

View 2 Replies View Related

Local Variable Assignment In CREATE TRIGGER

Mar 7, 2006

Hi Guys,

i'm batttling with the below Trigger creation

__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @v_object_key VARCHAR(80)
DECLARE @v_object_name VARCHAR(40)
DECLARE @v_object_verb VARCHAR(40)
DECLARE @v_datetime DATETIME

SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!!
SET @v_object_name = 'FochiniTable'
SET @v_object_verb = 'Create'
SET @v_datetime = GETDATE()

IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')

END
________________________________________________

i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id

and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]

any help will be appreciated
lehare.

View 1 Replies View Related

TSQL - Create A Variable Number Of Rows

May 4, 2008

I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :)
Thanks



ORIGINAL DATA
EventID StartDate EndDate
1 1/1/08 1/1/08
2 20/1/08 22/1/08


REQUIRED DATA
EventID EventDate
1 1/1/08
2 20/1/08
2 21/1/08
2 22/1/08

View 2 Replies View Related

Passing Variable Into CREATE LOGIN Script

Sep 23, 2013

Need to create same login on 100s of servers using local admin login create on the OS. (for xp_cmdshell proxy setup)

Normal command works fine but I meed to make <svr_name> a variable.

CREATE LOGIN [<svr_name>DBALocalUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

When I try various efforts to pass in the <svr_name> portion it fails every time.

declare @cmd varchar(200),
declare @username varchar(50)
set @username = 'DBALocalUser'
set @cmd = ' CREATE LOGIN ['+@@servername+''+@username'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'

[Code] .....

View 1 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go



Thanks Guys.

View 14 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Create Dts Variable For Date In Character Format Yyyymmdd

Nov 23, 1999

I'm trying to create a variable for use in a DTS job for yesterday's date in the string format YYYYMMDD for use in pulling yesterday's material transactions from my ERP system. I've got the Query statement created and working fine, but I have to manually go into the DTS job and insert the proper date manually. I'm looking into an Active-X script inserted into the workflow for the job to create a global variable, but I can't seem to come up with anything that works.
Any help or direction would be appreciated.
Thanx in advance!!
Glenn

View 1 Replies View Related

Unable To Create Variable Select Statement In For Each Loop

Apr 24, 2007

What I'm trying to do is this;

I have a table with Year , Account and Amount as fields. I want to



SELECT Year, Account, sum(Amount) AS Amt

FROM GLTable

WHERE Year <= varYear



varYear being a variable which is each year from a query



SELECT Distinct Year FROM GLTable



My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.



What I have done is to defined two variables as follows

Name: varYear (this will hold the year)

Scope: Package

Data type: String



Name:vSQL (This will hold a SQL statement using the varYear)

Scope: Package

Data type: String

Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]



I've created a SQL Task as follows

Result set: Full Result Set

Connection Type: OLE DB

SQL Statement: SELECT DISTINCT Year FROM GLTable

Result Name: 0

Variable Name: User::varYear



Next I created a For Each Loop container with the following parameters

Enumerator: Foreach ADO Enumerator

ADO Object source Variable: User::varYear

Enumeration Mode: Rows in First Table



I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows

Data Access Mode: SQL Command from Variable

Variable Name: User::varYear



However this returns a couple of errors "Statement(s) could not be prepared."

and "Incorrect syntax near '='.".



I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.



Any help would be most appreciated.

Regards,

Bill

View 5 Replies View Related

Integration Services :: C# Code To Create File From Variable Value?

May 5, 2015

My Execute SQL Task will store the file name into a variable(mFile) of type string datatype.

Now I wanted a script task (C# code) to create a filename from the variable (mFile) value.

Since it is a common issue I tried a lot in the internet but none of the queries worked.

View 10 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related







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