excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.