DSXchange

I have a column in a table that is autonumber. I dont want to input anything there so I set up the dirivation to @Null. The database doesnt like that at all. How do you handle an autonumber column in the transform?
Jim

It depends on the target database. Do it the same way that you would for the target database. For example, if the table contains columns AN1, C1, C2 and C3 (where AN1 is the autonumber column), you would either:
INSERT INTO table(C1,C2,C3) VALUES (?,?,?);
or
INSERT INTO table(AN1,C1,C2,C3) VALUES (0,?,?,?);

In the first example, you do not even supply a value for the autonumber column. The fact that a new row is being inserted triggers generation of the automatic number. This is the approach used, for example, by SQL Server.

In the second example, you supply the value zero for the autonumber column (which is usually called a SERIAL data type). This is the approach used, for example, by Red Brick Warehouse.

ray.wurlod wrote:It depends on the target database. Do it the same way that you would for the target database. For example, if the table contains columns AN1, C1, C2 and C3 (where AN1 is the autonumber column), you would either:
INSERT INTO table(C1,C2,C3) VALUES (?,?,?);
or
INSERT INTO table(AN1,C1,C2,C3) VALUES (0,?,?,?);

In the first example, you do not even supply a value for the autonumber column. The fact that a new row is being inserted triggers generation of the automatic number. This is the approach used, for example, by SQL Server.

In the second example, you supply the value zero for the autonumber column (which is usually called a SERIAL data type). This is the approach used, for example, by Red Brick Warehouse.

That does make sence but where in a transform stage would I put that?
I haevnt seen a place to do just sql...
Jim

JDionne wrote:

ray.wurlod wrote:It depends on the target database. Do it the same way that you would for the target database. For example, if the table contains columns AN1, C1, C2 and C3 (where AN1 is the autonumber column), you would either:
INSERT INTO table(C1,C2,C3) VALUES (?,?,?);
or
INSERT INTO table(AN1,C1,C2,C3) VALUES (0,?,?,?);

In the first example, you do not even supply a value for the autonumber column. The fact that a new row is being inserted triggers generation of the automatic number. This is the approach used, for example, by SQL Server.

In the second example, you supply the value zero for the autonumber column (which is usually called a SERIAL data type). This is the approach used, for example, by Red Brick Warehouse.

That does make sence but where in a transform stage would I put that?
I haevnt seen a place to do just sql...
Jim

I figured it out...I just exclude the column from the transform and I have no problems..Thanx Guys
Jim