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 creat
table names in a more efficient way.

Here is what happens now, everytime I do the analysis, I will create a
table called something like customer_20050930, and then update the
table by using several update steps. Then next month I will create a
table called customer_20051031. Does anyone know if there is a better
way to do it? like using a macro variable for the month-end date? Now
everytime 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 a
macro, I would only need to change it once.

Thanks!

View 8 Replies


ADVERTISEMENT

Variable Table Names

Jan 29, 2001

Can I do something like the following with SQL Server?

DECLARE @Table_Name varchar(100)

SET @Table_Name = 'Accounts'

SELECT * FROM @Table_Name

View 2 Replies View Related

Variable Table Names In DTS Package

Jul 20, 2005

I am attempting to export data on a daily basis via DTS to an XLspreadsheet. I would either like to: a) have a separate worksheet inthe spreadsheet for each export or b) a completely differentspreadsheet for each export. Whenever I attempt to use a variable asthe name of my table in the DTS package, it will not let me.Constructing a string and attempting to execute it also fails,although both of these work in query analyzer. Any advice?Peter

View 1 Replies View Related

Passing Variable Table Names To Stored Procedure

May 5, 2000

I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.

Select * from @Passedtablename. This won't work. Any insights.

View 1 Replies View Related

Xml File And Automatically Parse NODE Names Into A Memory Variable Table

Sep 13, 2014

Ok I am faced with working with XML on a regular basis, which is fine.

DECLARE @ViewSN INT
IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType>
insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments)
select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL

[code]...

What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.

View 7 Replies View Related

Create List Of Table Names And Size For A Database

May 5, 2004

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQL

View 12 Replies View Related

To Create Unique Table Names And Access Them In Code...

Jan 17, 2006

Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much

View 3 Replies View Related

How To Write A Script To Create A Table (With Unfixed Field Names)?

Mar 28, 2006

Hello All.

You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.

And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.

My below script shows error. I have following records in my TempWeekFile

Week_No WeekCnt
10 1
11 2
12 3
13 4


How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.

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

if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month

Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1

CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,

while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End

) ON [PRIMARY]
GO

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

View 3 Replies View Related

Power Pivot :: Can Create A New Table That Shows Only Distinct Names

May 5, 2015

My DB contains company names repeating themselves several times (in the same column).

Can I create a new table that shows only the distinct names, and use it to work with the data?

My intention is to allow my users to choose only from the options within the DB (mimic in a way the list validation option in excel)

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

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

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

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

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

Variable Column Names

Mar 15, 2004

I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...

Is there any way to accomplish this???

I thought about placing these values into a temp table...

This is what I have so far...

Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)

Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin

Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End

View 1 Replies View Related

SQL Doesn't Like Variable Names In Commands?

Jan 29, 2007

Hello,
Why would this issue syntax errors? How can it be made to work?

DECLARE @TKey as BigInt
DECLARE @TName as VARCHAR(32)
DECLARE @Tid AS VARCHAR(32)

SET @TKey=99
SET @TName='TestTABLE'
SET @Tid='TestTABLEID'

SET IDENTITY_INSERT @TName ON

INSERT INTO @TName (@Tid) VALUES (@TKey)

SET IDENTITY_INSERT @TName OFF

--PhB

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

Table Names And Field Names

Jan 21, 2004

I'm trying to do an update query that looks like this:

UPDATE

PAEMPLOYEE

SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL


FROM

PAEMPLOYEE A

JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE

It's erroring out on the Employee prefix B.EMPLOYEE saying:

..."does not match with a table name or alias name used in the query"


Is it wrong or will it cause problems to have a field name the same as the table name?

View 5 Replies View Related

Column Names From A Variable Without Execute Statement

Oct 11, 2002

Is there anyway anyone knows of that i can select columns using variable names without building an execute statement??

ie.

DECLARE @col varchar(10)

SELECT @col = "AuditID"

SELECT @Col FROM tblAudit

??

Anyhelp a bonus

Thanks

Daniel/

View 8 Replies View Related

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable

May 1, 2008

is it possible to use twice declared. Variable names-
declared. Variable and after KILL
and use the same declared. Variable
like

DECLARE

@StartDate datetime

KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2 big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error


The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate ??
KILL @StartDate ??


TNX

View 12 Replies View Related

Variable Names Must Be Unique Within A Query Batch Or Stored Procedure.

May 4, 2007

hi ,

i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...



The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.

thanx in advance

raj

View 5 Replies View Related

Create Login Names Without Spaces

Dec 31, 2004

Afternoon,

I've got this security database that needs to create a login name using first initial and 7 characters of the lastname. No problem.

I wrote this:

select
CASE WHEN LastName like '%.%' then
LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation

The replace and case functions I used to get rid of the '.' Like the name St. Clair now I get "GSt Clai" I need it to also get rid of the space but am stuck. Any thoughts would be appreciated.

Thanks

Laura

View 2 Replies View Related

CREATE VIEW - Script To Automate Column Names?

Mar 14, 2007

Hi,I'm trying to create views on all my existing tables and for that I'dlike to create a script or so.I don't want to specify the '*' for the columns in the create viewstatement. I prefer to specify the column names.I have the column names int sys.columns table but Do not know how tohandle them to have a statement like that:CREATE VIEW myVIEWWITH SCHEMABINDINGASSELECT col1name, col2name, col3name, etc...from sys.columns....?????.....Anyone can help?thx,Chris

View 1 Replies View Related

How Do I Create A Package That Imports An Excel File Where Column Names Are Changing.

Sep 29, 2006

Hi,

Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.

We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.

Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.

Any help appreciated,
TIA, Anthony.

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







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