about Tsql of Cursor

declare @.SourceName varchar(40)
select @.SourceName=@.SourcePre+'SizeDefine'
Declare MyCursor Cursor for
select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
@.SourceName
open MyCursor
--
@.SourceName is a table name, and depend on user input
the error is'Must declare the variable '@.SourceName'.'
i guess the varible @.SourceName need something to make
system think it is a table ,what shall i do?thanks a lotdo...
declare @.SourceName varchar(40)
select @.SourceName=@.SourcePre+'SizeDefine'
exec('Declare MyCursor Cursor for
select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
@.SourceName )
open MyCursor
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> declare @.SourceName varchar(40)
> select @.SourceName=@.SourcePre+'SizeDefine'
> Declare MyCursor Cursor for
> select
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
> @.SourceName
> open MyCursor
> --
> @.SourceName is a table name, and depend on user input
> the error is'Must declare the variable '@.SourceName'.'
> i guess the varible @.SourceName need something to make
> system think it is a table ,what shall i do?thanks a lot|||Why do you want to reference table names dynamically in this way? You appear
to have multiple tables of the same structure which you want to reference
with a single SELECT statement. This usually indicates a flawed design that
can be fixed by consolidating your tables into one. For example your tables
might look like this:
CREATE TABLE
Accounts_2001 (accountno INTEGER PRIMARY KEY, amount NUMERIC(10,2)...)
CREATE TABLE
Accounts_2002 (accountno INTEGER PRIMARY KEY, amount NUMERIC(10,2)...)
In which case you should combine them into a single table and add an extra
column to the primary key:
CREATE TABLE Accounts (yearno INTEGER CHECK (yearno BETWEEN 2000 AND 2100),
accountno INTEGER, amount NUMERIC(10,2)... , PRIMARY KEY (yearno,
accountno))
This is a much more effective design and your "@.source" parameter then just
becomes part of the WHERE clause of a SELECT statement.
Finally, why are you using a cursor? 99.99% of the time cursors are
unnecessary. You should always try to avoid cursors because of their poor
performance and their resource overhead. If you need help finding a
non-cursor solution then post your DDL (CREATE TABLE statements including
keys and constraints), include some sample data (INSERT statements) and also
post your cursor code.
--
David Portas
--
Please reply only to the newsgroup
--|||thanks a lot
and another question:)
declare @.S_nSizeID
exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName)
i want the value of @.S_nSizeID but the system tell
me @.S_nSizeID don't declare,how to change this script
thanks
>--Original Message--
>do...
>declare @.SourceName varchar(40)
>select @.SourceName=@.SourcePre+'SizeDefine'
>exec('Declare MyCursor Cursor for
>select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
G
>ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
>@.SourceName )
>open MyCursor
>
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> declare @.SourceName varchar(40)
>> select @.SourceName=@.SourcePre+'SizeDefine'
>> Declare MyCursor Cursor for
>> select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
>> @.SourceName
>> open MyCursor
>> --
>> @.SourceName is a table name, and depend on user input
>> the error is'Must declare the variable '@.SourceName'.'
>> i guess the varible @.SourceName need something to make
>> system think it is a table ,what shall i do?thanks a lot
>
>.
>|||thanks a lot
and another question:)
declare @.S_nSizeID
exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName)
i want the value of @.S_nSizeID but the system tell
me @.S_nSizeID don't declare,how to change this script
thanks
>--Original Message--
>do...
>declare @.SourceName varchar(40)
>select @.SourceName=@.SourcePre+'SizeDefine'
>exec('Declare MyCursor Cursor for
>select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
G
>ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
>@.SourceName )
>open MyCursor
>
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> declare @.SourceName varchar(40)
>> select @.SourceName=@.SourcePre+'SizeDefine'
>> Declare MyCursor Cursor for
>> select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
>> @.SourceName
>> open MyCursor
>> --
>> @.SourceName is a table name, and depend on user input
>> the error is'Must declare the variable '@.SourceName'.'
>> i guess the varible @.SourceName need something to make
>> system think it is a table ,what shall i do?thanks a lot
>
>.
>|||thanks
but my store procedure is to Export and change data
between database base,because the database name is
depend on user input,so the table name is different
and need split one table data to several tables
such as
--
Declare @.CTD_strID char(8)
Declare @.CTD_strDescription varchar(255)
Declare @.CTD_nSortNum smallint
Declare @.CTD_bIsValid char(1)
Declare @.CTD_nGridID int
Declare @.CT_nCategoryID int
exec('Declare MyCursor Cursor for '+
'select
CTD_strID,CTD_strDescription,CTD_nSortNum,CTD_bIsValid,CTD_
nGridID from '+@.SourceName)
open MyCursor
FETCH NEXT FROM MyCursor
INTO @.CTD_strID,
@.CTD_strDescription ,@.CTD_nSortNum,@.CTD_bIsValid,@.CTD_nGrid
ID
WHILE @.@.FETCH_STATUS = 0
begin
select @.DestName=@.DestPre+'CategoryTree'
exec('select '+@.CT_nCategoryID+'=isnull(max
(CT_nCategoryID),0)+1 from '+@.DestName)
exec('insert
into '+@.DestName+'(CT_nCategoryID,CT_strCode,CT_strDescript
ion,CT_nSortNum,CT_nParentID,CT_cIfValid,CT_dtModifyTime)'+
'values
('+@.CT_nCategoryID+','+@.CTD_strID+','+@.CTD_strDescription+'
,'+@.CTD_nSortNum+','+@.CT_nCategoryID_Parent+','+@.CTD_bIsVal
id+',getdate())' )
select @.DestName=@.DestPre+'CategoryInAdType'
exec('insert
into '+@.DestName+'(CIT_nCategoryTreeID,CIT_nAdTypeID)'+
'values('+@.CT_nCategoryID+','+@.CTD_nGridID+')' )
end
close MyCursor
deallocate MyCursor
--
do you have any ideas about it don't use cursor?
thanks a lot
>--Original Message--
>Why do you want to reference table names dynamically in
this way? You appear
>to have multiple tables of the same structure which you
want to reference
>with a single SELECT statement. This usually indicates a
flawed design that
>can be fixed by consolidating your tables into one. For
example your tables
>might look like this:
>CREATE TABLE
> Accounts_2001 (accountno INTEGER PRIMARY KEY, amount
NUMERIC(10,2)...)
>CREATE TABLE
> Accounts_2002 (accountno INTEGER PRIMARY KEY, amount
NUMERIC(10,2)...)
>In which case you should combine them into a single table
and add an extra
>column to the primary key:
>CREATE TABLE Accounts (yearno INTEGER CHECK (yearno
BETWEEN 2000 AND 2100),
>accountno INTEGER, amount NUMERIC(10,2)... , PRIMARY KEY
(yearno,
>accountno))
>This is a much more effective design and your "@.source"
parameter then just
>becomes part of the WHERE clause of a SELECT statement.
>Finally, why are you using a cursor? 99.99% of the time
cursors are
>unnecessary. You should always try to avoid cursors
because of their poor
>performance and their resource overhead. If you need help
finding a
>non-cursor solution then post your DDL (CREATE TABLE
statements including
>keys and constraints), include some sample data (INSERT
statements) and also
>post your cursor code.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||You will have to use sp_executesql to obtain the output.
e.g.
declare @.S_nSizeID int, @.sql nvarchar(1000)
set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1) from '+@.DestName
exec sp_executesql @.sql, N'@.S_nSizeID int output', @.S_nSizeID
select @.S_nSizeID
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:081501c3b009$4c086a40$a301280a@.phx.gbl...
> thanks a lot
> and another question:)
> declare @.S_nSizeID
> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> from '+@.DestName)
> i want the value of @.S_nSizeID but the system tell
> me @.S_nSizeID don't declare,how to change this script
> thanks
>
>
> >--Original Message--
> >do...
> >
> >declare @.SourceName varchar(40)
> >select @.SourceName=@.SourcePre+'SizeDefine'
> >
> >exec('Declare MyCursor Cursor for
> >select
> >SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
> G
> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
> >@.SourceName )
> >
> >open MyCursor
> >
> >
> >
> >--
> >-oj
> >RAC v2.2 & QALite!
> >http://www.rac4sql.net
> >
> >
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> >> declare @.SourceName varchar(40)
> >> select @.SourceName=@.SourcePre+'SizeDefine'
> >>
> >> Declare MyCursor Cursor for
> >> select
> >>
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
> >> @.SourceName
> >>
> >> open MyCursor
> >>
> >> --
> >> @.SourceName is a table name, and depend on user input
> >> the error is'Must declare the variable '@.SourceName'.'
> >> i guess the varible @.SourceName need something to make
> >> system think it is a table ,what shall i do?thanks a lot
> >
> >
> >.
> >|||with many thanks,oj
>--Original Message--
>You will have to use sp_executesql to obtain the output.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID
>select @.S_nSizeID
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> thanks a lot
>> and another question:)
>> declare @.S_nSizeID
>> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> from '+@.DestName)
>> i want the value of @.S_nSizeID but the system tell
>> me @.S_nSizeID don't declare,how to change this script
>> thanks
>>
>>
>> >--Original Message--
>> >do...
>> >
>> >declare @.SourceName varchar(40)
>> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >
>> >exec('Declare MyCursor Cursor for
>> >select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> G
>> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from '+
>> >@.SourceName )
>> >
>> >open MyCursor
>> >
>> >
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> declare @.SourceName varchar(40)
>> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >>
>> >> Declare MyCursor Cursor for
>> >> select
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from
>> >> @.SourceName
>> >>
>> >> open MyCursor
>> >>
>> >> --
>> >> @.SourceName is a table name, and depend on user input
>> >> the error is'Must declare the
variable '@.SourceName'.'
>> >> i guess the varible @.SourceName need something to
make
>> >> system think it is a table ,what shall i do?thanks a
lot
>> >
>> >
>> >.
>> >
>
>.
>|||hello oj:
i test the script you write ,but @.S_nSizeID
always return NULL,it seems the para output don't work,
ao you have any ideas?thank you
>--Original Message--
>You will have to use sp_executesql to obtain the output.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID
>select @.S_nSizeID
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> thanks a lot
>> and another question:)
>> declare @.S_nSizeID
>> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> from '+@.DestName)
>> i want the value of @.S_nSizeID but the system tell
>> me @.S_nSizeID don't declare,how to change this script
>> thanks
>>
>>
>> >--Original Message--
>> >do...
>> >
>> >declare @.SourceName varchar(40)
>> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >
>> >exec('Declare MyCursor Cursor for
>> >select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> G
>> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from '+
>> >@.SourceName )
>> >
>> >open MyCursor
>> >
>> >
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> declare @.SourceName varchar(40)
>> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >>
>> >> Declare MyCursor Cursor for
>> >> select
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from
>> >> @.SourceName
>> >>
>> >> open MyCursor
>> >>
>> >> --
>> >> @.SourceName is a table name, and depend on user input
>> >> the error is'Must declare the
variable '@.SourceName'.'
>> >> i guess the varible @.SourceName need something to
make
>> >> system think it is a table ,what shall i do?thanks a
lot
>> >
>> >
>> >.
>> >
>
>.
>|||Sorry for the late response...Anyway, I've left a key in that last post.
Please try this.
e.g.
declare @.S_nSizeID int, @.sql nvarchar(1000)
set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1) from '+@.DestName
exec sp_executesql @.sql, N'@.S_nSizeID int output', @.S_nSizeID OUTPUT
select @.S_nSizeID
HTH.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:006f01c3b0b2$d168cf00$a001280a@.phx.gbl...
> hello oj:
> i test the script you write ,but @.S_nSizeID
> always return NULL,it seems the para output don't work,
> ao you have any ideas?thank you
> >--Original Message--
> >You will have to use sp_executesql to obtain the output.
> >
> >e.g.
> >declare @.S_nSizeID int, @.sql nvarchar(1000)
> >
> >set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> from '+@.DestName
> >exec sp_executesql @.sql, N'@.S_nSizeID int output',
> @.S_nSizeID
> >
> >select @.S_nSizeID
> >
> >--
> >-oj
> >RAC v2.2 & QALite!
> >http://www.rac4sql.net
> >
> >
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:081501c3b009$4c086a40$a301280a@.phx.gbl...
> >> thanks a lot
> >> and another question:)
> >>
> >> declare @.S_nSizeID
> >> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> >> from '+@.DestName)
> >>
> >> i want the value of @.S_nSizeID but the system tell
> >> me @.S_nSizeID don't declare,how to change this script
> >> thanks
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >do...
> >> >
> >> >declare @.SourceName varchar(40)
> >> >select @.SourceName=@.SourcePre+'SizeDefine'
> >> >
> >> >exec('Declare MyCursor Cursor for
> >> >select
> >>
> >SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
> >> G
> >> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
> from '+
> >> >@.SourceName )
> >> >
> >> >open MyCursor
> >> >
> >> >
> >> >
> >> >--
> >> >-oj
> >> >RAC v2.2 & QALite!
> >> >http://www.rac4sql.net
> >> >
> >> >
> >> >
> >> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> >> >> declare @.SourceName varchar(40)
> >> >> select @.SourceName=@.SourcePre+'SizeDefine'
> >> >>
> >> >> Declare MyCursor Cursor for
> >> >> select
> >> >>
> >>
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> >> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
> from
> >> >> @.SourceName
> >> >>
> >> >> open MyCursor
> >> >>
> >> >> --
> >> >> @.SourceName is a table name, and depend on user input
> >> >> the error is'Must declare the
> variable '@.SourceName'.'
> >> >> i guess the varible @.SourceName need something to
> make
> >> >> system think it is a table ,what shall i do?thanks a
> lot
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||thank you very much!
>--Original Message--
>Sorry for the late response...Anyway, I've left a key in
that last post.
>Please try this.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID OUTPUT
>select @.S_nSizeID
>HTH.
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:006f01c3b0b2$d168cf00$a001280a@.phx.gbl...
>> hello oj:
>> i test the script you write ,but @.S_nSizeID
>> always return NULL,it seems the para output don't work,
>> ao you have any ideas?thank you
>> >--Original Message--
>> >You will have to use sp_executesql to obtain the
output.
>> >
>> >e.g.
>> >declare @.S_nSizeID int, @.sql nvarchar(1000)
>> >
>> >set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)
+1)
>> from '+@.DestName
>> >exec sp_executesql @.sql, N'@.S_nSizeID int output',
>> @.S_nSizeID
>> >
>> >select @.S_nSizeID
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> >> thanks a lot
>> >> and another question:)
>> >>
>> >> declare @.S_nSizeID
>> >> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> >> from '+@.DestName)
>> >>
>> >> i want the value of @.S_nSizeID but the system tell
>> >> me @.S_nSizeID don't declare,how to change this script
>> >> thanks
>> >>
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >do...
>> >> >
>> >> >declare @.SourceName varchar(40)
>> >> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >> >
>> >> >exec('Declare MyCursor Cursor for
>> >> >select
>> >>
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> >> G
>> >> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
>> from '+
>> >> >@.SourceName )
>> >> >
>> >> >open MyCursor
>> >> >
>> >> >
>> >> >
>> >> >--
>> >> >-oj
>> >> >RAC v2.2 & QALite!
>> >> >http://www.rac4sql.net
>> >> >
>> >> >
>> >> >
>> >> >"frank" <anonymous@.discussions.microsoft.com> wrote
in
>> >> message
>> >> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> >> declare @.SourceName varchar(40)
>> >> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >> >>
>> >> >> Declare MyCursor Cursor for
>> >> >> select
>> >> >>
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
>> from
>> >> >> @.SourceName
>> >> >>
>> >> >> open MyCursor
>> >> >>
>> >> >> --
>> >> >> @.SourceName is a table name, and depend on user
input
>> >> >> the error is'Must declare the
>> variable '@.SourceName'.'
>> >> >> i guess the varible @.SourceName need something to
>> make
>> >> >> system think it is a table ,what shall i do?
thanks a
>> lot
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>

about tsql

in tsql,how can select the data rely on the column order
,not the column name
such as
select column1,column2
from...You may need to pur like this ... select col1,col2 from
table name order by 1,2
Hope, I understood the question correctly.
Suresh
>--Original Message--
>in tsql,how can select the data rely on the column order
>,not the column name
>such as
>select column1,column2
>from...
>.
>|||Why would you want to do this' If you could state your problem, maybe we
can give you an alternate solution.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> in tsql,how can select the data rely on the column order
> ,not the column name
> such as
> select column1,column2
> from...
>|||i want to get several tables' first column's data
and the table name store in one table, and of course the
table's first column's name is different
in short
i want use a loop update several table's first column
>--Original Message--
>Why would you want to do this' If you could state your
problem, maybe we
>can give you an alternate solution.
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> in tsql,how can select the data rely on the column order
>> ,not the column name
>> such as
>> select column1,column2
>> from...
>
>.
>|||In the SQL language, you refer to columns by name, not position. There are two exceptions: "SELECT
*" and INSERT without a column name list.
You can use the INFORMATION_SCHEMA views to read each table name and the name of the first column
name. And based on that, you can construct your SQL statement and run that through dynamic SQL (EXEC
@.sql). The info schema views are documented in Books Online and I've done a diagram of them at
http://www.dbmaint.com/info_schema.asp.
You can find a lot of info on dynamic SQL at:
http://www.algonet.se/~sommar/
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
> i want to get several tables' first column's data
> and the table name store in one table, and of course the
> table's first column's name is different
> in short
> i want use a loop update several table's first column
>
>
> >--Original Message--
> >Why would you want to do this' If you could state your
> problem, maybe we
> >can give you an alternate solution.
> >--
> >HTH,
> >SriSamp
> >Please reply to the whole group only!
> >http://www32.brinkster.com/srisamp
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> >> in tsql,how can select the data rely on the column order
> >> ,not the column name
> >>
> >> such as
> >> select column1,column2
> >> from...
> >>
> >
> >
> >.
> >|||>--Original Message--
>In the SQL language, you refer to columns by name, not
position. There are two exceptions: "SELECT
>*" and INSERT without a column name list.
thanks a lot
>You can use the INFORMATION_SCHEMA views to read each
table name and the name of the first column
>name. And based on that, you can construct your SQL
statement and run that through dynamic SQL (EXEC
>@.sql). The info schema views are documented in Books
Online and I've done a diagram of them at
>http://www.dbmaint.com/info_schema.asp.
>You can find a lot of info on dynamic SQL at:
>http://www.algonet.se/~sommar/
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
>> i want to get several tables' first column's data
>> and the table name store in one table, and of course the
>> table's first column's name is different
>> in short
>> i want use a loop update several table's first column
>>
>>
>> >--Original Message--
>> >Why would you want to do this' If you could state your
>> problem, maybe we
>> >can give you an alternate solution.
>> >--
>> >HTH,
>> >SriSamp
>> >Please reply to the whole group only!
>> >http://www32.brinkster.com/srisamp
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> >> in tsql,how can select the data rely on the column
order
>> >> ,not the column name
>> >>
>> >> such as
>> >> select column1,column2
>> >> from...
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

about subsuery of tsql (or anything like oracle's sequence in sql server )

exec('insert
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

About removal of TimeStamp from DateTime part in TSQL.

Hi friends,
Does anybody could say how to remove the TimeStamp from DateTime part
in TSQL.
Thanks&Regards
Sunil.
Sunil a crit :
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>
Example, actual date without time :
SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME) AS
"CURRENT_DATE"
CURRENT_DATE
2006-02-15 00:00:00.000
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Removing is not possible if you use a timestamp, though you can set it
to 00:00:00, but eleminating is NOT possible. Time is always stuck to
date and vice versa. If you want the two things separated, you have to
use a CHARACTER Storage like VARCHAR, then you can CONVERT the datetime
to any non-containing time-format.
HTH, jens Suessmeyer.
|||Hi
The time part of a datetime will always be stored in the database, even if
you do not specify it when you insert the date it will default the time to
00:00:00. For display purposes you can use the CONVERT function to display
only the date portion of the datetime in a string format. See Books Online
for more information.
John
"Sunil" wrote:

> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>

About removal of TimeStamp from DateTime part in TSQL.

Hi friends,
Does anybody could say how to remove the TimeStamp from DateTime part
in TSQL.
Thanks&Regards
Sunil.
http://www.karaszi.com/SQLServer/inf...dOfTimePortion
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sunil" <nsunildutt@.gmail.com> wrote in message
news:1140016409.964263.61880@.f14g2000cwb.googlegro ups.com...
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>
|||Thanks all .
Tibor provided an excellent link.
|||Thanks all .
Tibor provided an excellent link.

About removal of TimeStamp from DateTime part in TSQL.

Hi friends,
Does anybody could say how to remove the TimeStamp from DateTime part
in TSQL.
Thanks&Regards
Sunil.http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sunil" <nsunildutt@.gmail.com> wrote in message
news:1140016409.964263.61880@.f14g2000cwb.googlegroups.com...
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>|||Thanks all .
Tibor provided an excellent link.|||Thanks all .
Tibor provided an excellent link.

About removal of TimeStamp from DateTime part in TSQL.

Hi friends,
Does anybody could say how to remove the TimeStamp from DateTime part
in TSQL.
Thanks&Regards
Sunil.Sunil a écrit :
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>
Example, actual date without time :
SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME) AS
"CURRENT_DATE"
CURRENT_DATE
---
2006-02-15 00:00:00.000
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Removing is not possible if you use a timestamp, though you can set it
to 00:00:00, but eleminating is NOT possible. Time is always stuck to
date and vice versa. If you want the two things separated, you have to
use a CHARACTER Storage like VARCHAR, then you can CONVERT the datetime
to any non-containing time-format.
HTH, jens Suessmeyer.|||Hi
The time part of a datetime will always be stored in the database, even if
you do not specify it when you insert the date it will default the time to
00:00:00. For display purposes you can use the CONVERT function to display
only the date portion of the datetime in a string format. See Books Online
for more information.
John
"Sunil" wrote:
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>

About removal of TimeStamp from DateTime part in TSQL.

Hi friends,
Does anybody could say how to remove the TimeStamp from DateTime part
in TSQL.
Thanks&Regards
Sunil.http://www.karaszi.com/SQLServer/in...idOfTimePortion
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sunil" <nsunildutt@.gmail.com> wrote in message
news:1140016409.964263.61880@.f14g2000cwb.googlegroups.com...
> Hi friends,
> Does anybody could say how to remove the TimeStamp from DateTime part
> in TSQL.
> Thanks&Regards
> Sunil.
>|||Thanks all .
Tibor provided an excellent link.|||Thanks all .
Tibor provided an excellent link.