Hi
How can i Create a table with variable name
eg
suppose variable x=tab1
I want the table created to be tab1 if x=tab2 then table created shd be tab2.. so how can this be dynamically done(creation of tables as well as stored procs)
i tried : create table @x .. it doesnt seem to work
please help
regards
This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed.
Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression?
I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...
Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
Can anyone out there help me?
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.
Hi,
We are building an application with
a database that contains Jobs. These Jobs have properties like Name, Code etc.
and some custom properties, definable by the application admin. For bulk import
of Jobs, we want to allow the import of an Excel sheet with the columns Name,
Code and a variable amount of columns. If the header names of these columns in
the Excel sheet match the name of a custom property in the system we want to add
the value of that cell into the database as property
value.
In our Data Flow of our Import
Package in SSIS we added an Excel Source that points to a test excel sheet with
the Name and Code columns and €“ for this example - 3 custom property columns
(Area, Department, Job Family). When we configure the Excel Source in the Excel
Source Editor, we have the option to select the Columns from the Available
External Columns table. But here lays the problem, we do not know at design
time, what custom property columns to expect. We DO expect the Name and Code
columns, but the rest is uncertain at design-time.
That raises the question: Is there
some way to select all of any incoming columns (something like a SELECT * in
T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is
being generated at design-time would need to be updated at run-time to reflect
the variability of the columns that might be encountered while reading the excel
sheet.
Then, we thought, we could add a Script
Component to our data flow that passes some kind of DataSet (or DataReader) in
which we can walk through the columns ourselves? But then still, we miss the
option to include ANY of the columns while reading an Excel sheet (or any other
datasource by the looks of it)
We are aware of the option of
optional columns in combination with the RaggedRight option, but it seems that
we would have to put all of the columns of a row in just one column and then
extract all the columns later with Derived Columns. But then, since the source
import file is being prepared by an application admin, we want don€™t want to
burden him with this horrendous task of putting everything in one
column.
We would like to have some way of
iterating through all the columns, either in a Script Component or maybe with a
Pivot/Unpivot mechanism.
Does anyone have any suggestions? Are there other options we should have considered?
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedBanti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedI 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.
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
View 2 Replies View RelatedHere is my code
ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Thanks
Ganesh
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
Hi there,
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN
CREATE INDEX resultcol2_ind ON @Result
-- do some other stuff
RETURN
END
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'))
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
I am using a Stored procedure in which I am creating a table and dropping that table at the end of execution. This SP is calling every 10 second (but no concurrent access) from my application. Is there any issue using the drop table command in the SP? will it create any memory fragmentation issue in SQL server?
View 7 Replies View RelatedHi 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 RelatedSQLLY challenged be gentle --Trying to create code that will drop a table using a variable as theTable Name.DECLARE @testname as char(50)SELECT @testname = 'CO_Line_of_Business_' +SUBSTRING(CAST(CD_LAST_EOM_DATEAS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),4)+ '_' + 'EOM'FROM TableNamePrint @testname = 'blah...blah...blah' (which is the actual tablename on the server)How can I use this variable (@testname) to drop the table? Undersevere time constraints so any help would be greatly appreciated.
View 2 Replies View RelatedIn 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.
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.
Code Snippet
declare @IdTable table
(
EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )
Any help is very much appreciated.
Andrew
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?
ALTER PROCEDURE [dbo].[rpt]
(
@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'
)
AS
--split CourtIDs into a table
DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Hi All,
I have a situation with a table that was created for a transactional
system with a 3 columns key. The table is similar to the following:
countrystatecitydescription
11221City A from country 1 and state 12
11321City A from country 1 and state 13
21422City B from country 2 and state 14
21522City B from country 2 and state 15
Now I'm trying to create a dts package that would allow me to build a
city dimension table with unique codes (keys) for each city. What kind of
transformation should I use to translate the old codes (based on the
country-state-city key) into the new ones and preserving the data
integrity?
Thanks,
Ignacio
Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)
I'm trying to create programmatically a table in my db on sql server 2005.1. I created a string "CREATE TABLE ....." and then tried to execute it using my ADO. That didn't work, so I copied the string into a query in sql server and got the message "CREATE TABLE sql is not supported". Why's that? 2. I created a stored procedure in my db that will get the "CREATE TABLE...." string and execute it. I thought it would look something like this: parameter @createString varchar(MAX) BEGIN BEGIN EXEC @createString or @createString END END Both these options didn't work.How can I make it work properly?Thanks.
View 3 Replies View RelatedI need a module to help me create a SQLServer database table. Is there a module that allows users to specify parameters in the creation of a SQLserver table?
View 1 Replies View RelatedHello, folks.
I wonder whether I can create a table based on the script in SQL Analyzer.
If this is possible, can you folks tell me how to do it.
Thanks in advance.
Hyunhyo Jun
GIS Research Group
University of Colorado
Does anyone know if it is possible to create a tmp table in RAM without pinning it and how to do it? I already knew that SQL has a stored procedure allows pinning a table in RAM.
Thank you for any input.
Hi
I am trying to create a table in sql server 2000 and seem to be getting an error
the code that i am using is as folows:
CREATE TABLE Invoice
(
InvoiceID INT IDENTITY PRIMARY KEY NOT NULL Auto_Increment,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)
I want to be able to have the invoiceid as the primary key that auto increments...
After creating the table id like to import a csv file with only the following data
Invoice_no, RefText, CustomerDetails
is it possible to do this as i want the id to automatically be generated to be unique
thanks
vijay writes "how can i creat a new table as i have in my database"
View 2 Replies View Related