Dynamic Table Name With GetDate()

Jun 17, 2008

I'm trying to work on an Integration Services project, and want to have a table copied into another table, but this destination table needs a datetimestamp in the name, like tbl061708547pm.

I can get this to work, but if I try to use it with an into statement and a "+", I get an error telling me something is wrong around the "+":

DECLARE @Date char(23)
SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)
SELECT colA, @Date FROM myTBL

*****
Error with:
DECLARE @Date char(23)
SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)
SELECT * into @Date FROM myTBL

...and error with:
DECLARE @Date char(23)
SET @Date = CONVERT(char(23), GETDATE(), 14)
SELECT * into 'myTBL_ + @Date FROM myTBL;

View 3 Replies


ADVERTISEMENT

Dynamic Table Name With GetDate()

Jun 17, 2008

I'm trying to work on an Integration Services project, and want to have a table copied into another table, but this destination table needs a datetimestamp in the name, like tbl061708547pm.
I can get this to work, but if I try to use it with an into statement and a "+", I get an error telling me something is wrong around the "+":
DECLARE @Date char(23)SET  @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT colA, @Date  FROM myTBL
*****Error with:DECLARE @Date char(23)SET  @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT * into @Date  FROM myTBL
...and error with:DECLARE @Date char(23)SET  @Date = CONVERT(char(23), GETDATE(), 14)SELECT * into 'myTBL_ + @Date FROM myTBL;

View 2 Replies View Related

Select * From Table Where Orderdate = Getdate()-1

Jun 1, 2007

i have a query

select * from table where orderdate = getdate()-1

I get no result

when i do select * from table where orderdate = '5/31/2007'

i get 208 rows returned. is there a way to get it using getdate()-1
so that i can use it in a procedure. i know i am doing something wrong.


Ashley Rhodes

View 7 Replies View Related

Delete From Table Based On Getdate

Oct 29, 2007

Greetings!

Need some assistance to:

Delete records from a table; keying off of a colum (expire date) thats data-type is datetime.

I would like to use command getdate less 2 years. So - I want to delete all records from a table where the expire date is 2 years older than the current date.

Appreciate the help!

roscoeLL9

View 4 Replies View Related

Getdate() >= Startdate And Getdate() <= Enddate

Oct 4, 2000

Please i need an exmple of ur solution, thanks :)

I'm using some files to show certain pages on certain date for an example

File name : aa.doc
start date: 10/02/00
end date : 10/03/00

But it expires on 10/02/00, here is the strored procedure:

Before the date comes, it expires the page
Here is my stored procedure:

"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "

Thankx a lot

View 1 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

Dynamic Table Name (was Please Help !! SQL)

Mar 2, 2005

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.






create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'


DECLARE @cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @cur_tab_name

WHILE (@@FETCH_STATUS =0)
BEGIN
DECLARE @count integer

select @count = count(*) from @cur_tab_name
INSERT INTO temp_tab
(@cur_tab_name, @count)

FETCH curREVIEW INTO @cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEW

View 1 Replies View Related

Dynamic Table Name

May 16, 2007

Hi..,

I want to know how to create a DTS for dynamic tables..,
Details:
1)I have some tables which created every month(Like [Name][2 char of month][2 char of year] eg: name0206 this table is for feburary 2006),i want to create dts for those tables

View 3 Replies View Related

Dynamic Table Name And Fields

Jan 13, 2007

I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName.  Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name.  So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop  that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email

View 3 Replies View Related

Dynamic Table Creation

Apr 19, 2007

Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
  This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks. 

View 1 Replies View Related

Dynamic Table Name Creation...

Jun 19, 2007

Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot  declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here 

View 6 Replies View Related

Dynamic SQL And Table Variables...?

Aug 23, 2004

Hello, all. I'm attempting to insert data into a table variable using dynamic SQL created at runtime.

However, with a Table variable, SQL server will not allow the EXEC method to be used in an INSERT statement.

How do I go about this?

View 2 Replies View Related

Dynamic Sql - Inserting Into A Table

Jun 22, 2001

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats(
@Field_NameVARCHAR(25),
@Team_IdINTEGER,
@TotalINTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"

View 3 Replies View Related

Dynamic SQL- Create Table

Sep 26, 2001

Is it possible to create a temp table using exec. e.g

exec('create table #temp1 (xyz int, abc int)')

when I run the above statement, I dont get an error but nothing happens.

The reason I need to create it dynamically is that in my stored proc, I am passed a number. This number determines how many columns my temp table should have.
thanks
Zoey

View 2 Replies View Related

Dynamic Table Naming From Sp

Sep 22, 2000

Does anyone know if it is possible to create dynamically named tables from within a stored procedure? The goal is to append a unique identifier on the end of an otherwise static table name to allow for multiple incarnations of the table to exist concurrently while not interfering with each other. For example, we would like to create and use a temp table that is suffixed with a login name to or a timestamp to make it unique.

Is this possible?

View 6 Replies View Related

Temp Table Using Dynamic Sql?

May 19, 2005

Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.

declare @str1 varchar(80),@str2 varchar(80)

set @str1='create table #tmp(col1 int)'
set @str2='insert into #tmp values (10)'

exec (@str1)
exec (@str2)

View 2 Replies View Related

Dynamic SQL Create Table

Oct 13, 2005

The following dynamic SQL script works for creating a table on the fly but if I change the select @tmpTblNm = 'tmpABC' to select @tmpTblNm = '#tmpABC'
it will not create the temp table. Can anyone help on creating a temp table dynamiclly?

declare @tmpTblNm varchar(40),
@str varchar(1000)

select @tmpTblNm = 'tmpABC'
select @str = ''

-- Create a temp table to hold the current page of data
-- Add an ID column to track the current row
select @str = 'Create Table '+ @tmpTblNm +
' ( row_id int IDENTITY PRIMARY KEY,
customerID int,
customerName varchar(40),
address1 varchar(40),
city varchar(25) )'

exec (@str)

View 1 Replies View Related

Dynamic Table Creation

Mar 19, 2004

Suppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102.
Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way?
Subhasish

View 3 Replies View Related

Selecting From A Dynamic Table Name

Sep 25, 2015

I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.

The query is as follows:-
UPDATE dbo.tbl_Process_List_Control_Table
SET LastUpdateDateTime = (
SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime)
FROM [wtbl_Process_List_Patient]
)
WHERE ProcessList = @ProcessName
This is a called proc with the following parameters:-
@LastUpdateDateTime is the date of the last record to be loaded
@ProcessName is the name of the process that was started/finished.

I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).

View 6 Replies View Related

Dynamic SQL And Table Names

Jun 5, 2008

Hi all,

I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.

According to all of the searches and articles I have read today, the following should work:

DECLARE @rownumINT
DECLARE @tabNamNVARCHAR(100)
DECLARE @SQLStringNVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'

SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'

EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum

However, it fails with:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".

Anybody know why?

Cheers

View 1 Replies View Related

Dynamic Drop Table

Jan 24, 2007

Hi

I am trying to use sql injection to create a dynamic drop table statment. I have tried to reformat a bunch of ways but can not get it. What am I doing wrong?

set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N' + @@tblname + ') and OBJECTPROPERTY(id, N' + 'IsUserTable' + ') = 1)'
drop table @@tblname
execute(@sql)

View 2 Replies View Related

Dynamic Create Table

Apr 2, 2007

Hi guru !!

I want to create a table that can created dynamicly based on front end application.table should handle insertion and edit also. please help me to get out of this situation.

thanks
shekhar

View 2 Replies View Related

Copy Dynamic Table?

Aug 20, 2007

I have an Oracle 10G database with some tables that I want to load into my SQL 2005 database every night (Datawarehouse). The tables may change(add columns) and this could be done in SQL 2000 DTS by writing a script as transformation. How can I do this in SSIS?

View 1 Replies View Related

Dynamic Table In Report

Nov 8, 2006

hi,

i have a dataset which returns me a dynamic set of data ie. number of columns can be different....say 14 columns for some dates...and just 12 for some other...(pivot table is being used in the backend)...how can i present this in the table...also my column names are best suited to be the column headers in the table.....is it possible to create a table at runtime...??

View 5 Replies View Related

Creating Dynamic Table

Oct 29, 2007

Hello all,

I have the following situation.
Our customer would like to generate a raw data export report from specific customer databases.

I have an administrative database with a customer table. In this customer table each customer has it's on specific
customerId guid.
Each customer also has it's own customer database that is named: Customer_ + guid of the customer in the admin
database (eg: Customer_982240e79f424fb0a1d4bed16267245d).
This customer database has 1 main table (dbo.Data), but the design can be different depending on the customer.
Our customer would like to have the possibility to select specific fields from the table to export to an excel file.
So i never know in advance how much fields there will be in the table.

What i want to achieve is to generate a report where i can select a specific customer from a list (from the admin database). Then be able to view all the available fields inside the table (like a dataset or something) and then let the
customer select which fields he wants in his data export report.

Can this be achieved in RS2005?

Greetz
Vinnie

View 3 Replies View Related

Dynamic Table Name In Destination

Feb 8, 2007

How to create a new table dynamically in OLE DB destination.

This is what i am doing

I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.

Thanks in advance for any insight on how to make this work.

-Amar

View 15 Replies View Related

Creating Table Using Dynamic SQL

Jul 26, 2007

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @TopRange int
Declare @BottomRange int
Declare @SQL Varchar(1000)
IF @Param_leadage = '91+'
SET @TopRange = 91
ELSE
Set @TopRange = RTRIM(LEFT(REPLACE(@Param_leadage,'-',''),2))

IF @Param_leadage = '91+'
SET @BottomRange = 4000
ELSE
Set @BottomRange = LTRIM(RIGHT(REPLACE(@Param_leadage,'-',''),2))

SET @SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@BottomRange)
IF @Param_status = 'Online Churn'
SET @SQL = @SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @SQL = @SQL + ' AND dbo.tblCustomer.idStatus = ' + @Param_idstatus

EXEC(@SQL)

View 6 Replies View Related

Create Dynamic Table From CSV

Apr 3, 2007

Hi,

I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:

Time, Probe1, Probe2, Probe3, Probe4

Whereas the next month I can have something like this

Time, Probe2, Probe3, Probe1, Probe4



The "Time" column will always be on the left side, but the probes may vary in their placement.

I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.

Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?

Any help appreciated!

View 11 Replies View Related

Dynamic Table Transfer

May 7, 2008

I've got a DB2 database that contains a lot of tables. I need to extract the data from some of them and put them in a SQL Server database. The number of tables needed may change, so I need an easy way of controlling this.

So, I created a lookup table in the target SQL Server DB that lists the tables I want with any selection criteria needed.
I have a For Each loop that goes through every row in the table. using the current row, it deletes the contents of the destination table (done and working), and then tries to load the source into it.The destination OLE DB uses a variable for the table name to insert into.

The problem I've got is whilst I can set up the OLE DB Source to use a SQL command as variable, I have to do it in the advanced editor or in the properties, as the normal editor gives an OLE DB Error. This is probably due to the fact that the variable has nothing in it at this point. I've turned EvaluateExternalMetaData off to avoid any design time errors, but when attempting to run I still get :




Code Snippet
[DTS.Pipeline] Error: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.



Which I understand, as there are none until runtime. How do I get around this problem of not having any columns to map until I know which table I am processing?

Thx

Rob

View 3 Replies View Related

Partition Table On Dynamic Value.

Apr 24, 2007

I would like to partition a table having only items posted in the last 24 hours in one filegroup and all others in another. Is there a way that my partition function could contain something like "col1 >= dateadd(d,-1,getdate())" and have items move to the secondary filegroup as they age pst 24 hours?



Thanks

View 7 Replies View Related

How To Make Table Name Dynamic

Dec 31, 2007



Hi,

In my application i need to access mutiple table. I'm writing a stored procedure
in which i need to access tables such as TB01,TB02 .. Basically TBFY
where FY is parameter.

I tried for OPENQUERY, but that needs me to add a linked server, which i don't
think is a good idea.

Can anyone suggest on how can i do so?


I'm using SqlServer2000.

Thanks.

View 6 Replies View Related

Dynamic Create Table

Oct 30, 2006

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....



Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address



Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

View 7 Replies View Related

How To Make Table Name Dynamic

Nov 19, 2007

Hi,

I have a requirement where i need to build the table name dynamically in the following queries.


declare @REF_DATA_TYPE nvarchar(20)

set @REF_DATA_TYPE='COUNTRY'



these are 4 cases where i need to use the table name dynamically

1. IF exists(select 1 from 'MD_REF_'+@REF_DATA_TYPE where code=@code_T)

2. Update 'MD_TB_REF_'+@REF_DATA_TYPE

3. from @ACTUAL_DATA p join 'MD_REF_'+@REF_DATA_TYPE T on T.code=P.code

4. INSERT INTO 'MD_REF_'+@REF_DATA_TYPE(Code,[Name],Description)


But i am getting error when i do this.

Please let me know what to do to solve this

Thanks in advance

View 5 Replies View Related







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