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