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
autonumber column????
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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