Easy Insert Statement ... Hopefully!

In Oracle I have an insert statement where Im selecting from one table and isnerting into another. One of the columns Im inserting is the current date/time

Ex.

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',SYSDATE,'1'

from REGION;

In SQL Server 2005 I cannot seem to find the right fit for the SYSDATE command: Please help

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',?,'1'

from REGION;

use GETDATE()

If you can use the same datetime for every row I would do this because it is much faster:

DECLARE @.curdate DATETIME
SET @.curdate = GETDATE()

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',@.curdate,'1'

from REGION;

|||AND not subject ot time variations.|||

Great!!! Exactly what I was looking for

Thanks

Dynamically selecting table in DTS

Hello,

I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.

Thanks

MV

Are you trying to do this in a bulk insert task or data pump? If you're using the bulk insert task, you can set a connection and the properties of the bulk insert task to be dynamic by using the dynamic properties task. If you're using the data pump, it's slightly more complex. You'll have to use an ActiveX Script task to also set the properties of the destination table to concatenate whatever database you'd like to load to the table name. It's been a while since I looked at the code but it isn't too bad (about 4 lines or so). OR, you can just convert to SSIS :).

Brian

|||

Thanks

i got the way

Dynamically Selecting columns

Hi all,

I need a select statement. In that select statement's column_list, I dont need a column, in such a way that column can be among a list of the available columns in the table. That means suppose there are column names with names A,B,C,D,E,F. I need to write a select statement in such a way,

select column_Name from table_Name

In the above column_name could be any among A,B,C,D,E,F, that could be decided programmatically.

Please help me. Very Urgent.

Chandu

Hi,

Try this.

Code Snippet

DECLARE @.SQL nvarchar(500)

DECLARE @.column nvarchar(100)

DECLARE @.table nvarchar(100)

set @.column = '*'

set @.table = 'table'

SET @.SQL = 'SELECT ' + @.column + ' FROM ' + @.table

EXEC sp_executesql @.SQL

regards,

Janos

|||

Chandu,

Using dynamic SQL, as Janos demonstrated, is the way to accomplish your task.

However, there are significant security cautions about using dynamic SQL. This article by Erland (and a couple from Microsoft) will go into more detail about using dynamic SQL and how to protect against most of the hazards.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

Dynamic table names

Hi all,
how can I generate a temporary table name dynamically? I am selecting
and idnumber from another table. and I need to insert bunch of data to
a #Temptable but it must have that idnumber at the end.
Any ideas?
t.Uh, why not put the id number in a column instead of making it part of the
name?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1169244127.681152.279510@.a75g2000cwd.googlegroups.com...
> Hi all,
> how can I generate a temporary table name dynamically? I am selecting
> and idnumber from another table. and I need to insert bunch of data to
> a #Temptable but it must have that idnumber at the end.
> Any ideas?
> t.
>