Data Pipelines and identity columns

[Todd Pietrowski]

I am am trying to insert the results of a query from a Sybase database into
a MS Sql Server table using a data pipeline. The problem I run into is that
I don't want to a column from the query to the identity column in the
destination table. Is there anything I can do?

Example:
source - query returns: name, address, zipcode
dest - SQL Server table has columns: id, name, address, zipcode

Powerbuilder's data pipeline painter group them as follows
Source Dest
name id
address name
zipcode address
zipcode

Is there another way to do this if the pipeline doesn't work.

Thanks in advance,

Todd Pietrowski
IT Analyist, 3M Corp
tod...@mmm.com

Opinions expressed herein are my own and may not represent those of my employer.

[Dean Ross]

Todd,

I had a similar problem in moving an entire database. I solved this by
creating named computed NULL or empty string columns in the source select so
they would match up with the new table columns. Something like this:

SELECT NULL no_id, // null value
name,
address,
zipcode
'' user_id // empty string
FROM USER;

Hopre it helps,

Dean Ross,
CTG
dean...@ctg.com

[Peng Xiaobing]

One thing you can try:
After you select the source table and columns, change the destination type
of the column which will be identity column, for example, change "int" to
"identity int" from the dropdown list, the pipeline will not insert data to
the identity column.

Todd Pietrowski wrote in message <62g85c$b...@dawn.mmm.com>...

[Bruce Johnson]

Make a view of the table in the SQL Server using:
(This is Sybase syntax, but I'm pretty sure it's similar in SQL Server)

CREATE VIEW dest_view
AS SELECT name,
address,
zipcode
FROM dbo.dest

And use dest_view instead of dest as the target of the pipeline.

Works for me....this very moment I'm working on an app that does
something like 8 pipelines into the same table, only different fields
each pipeline...this requires a different view for each pipeline.

identity columns automatically generate themselves on insert, so you
generally don't _want_ to insert anything into them.

(This is Sybase syntax, but I'm pretty sure it's similar in SQL Server)