topic Re: How to Create MS SQL Server Table With PK and Insert into it in Talend Studio

How to Create MS SQL Server Table With PK and Insert into it

_AnonymousUser — Sat, 16 Nov 2024 13:26:35 GMT

I cannot get this to work no matter what I try.
I want a tMSSqlOutput component to "Create table if not exists" (because the table will not initially exist). The tMap feeding into the tMSSqlOutput has all of the columns I want to go into the DB with the exception of an "id" column which is to serve as an auto-increment PK. I don't know how to get the "id" column in there. I have tried everything. This is a really simple concept, but I find this impossible to do in talend.

Re: How to Create MS SQL Server Table With PK and Insert into it

_AnonymousUser — Tue, 04 May 2010 20:21:10 GMT

If I try adding an "id" to the schema coming out of my tMap and to the tMSSqlOutput schema, I get hit with a
"Identity column 'id' contains invalid INCREMENT." error. There's no where to specify an increment value in the schema editor!!! I can say the column is the key, I can say the DB type is INT IDENTITY, I can say the column is non-nullable, I can specifiy a length, precision, default, or comment...but no INCREMENT.
So then, if I go into my tMSSqlOutput component and check the "Specify identity field", logically I think that maybe if I specify the "Step" to be 1, that'll set the INCREMENT. Sounds like that should do the trick right? WRONG. I get another error. This time a "More than one column IDENTITY constraint specified for column 'id'". I never specified multiple identity columns. In the schema I said I wanted the "id" column to be my PK and an INT IDENTITY type. In the tMSSqlComponent I checked "Specify identity field", chose my "identity field" to be my "id" column, and set the step to 1. Where am I supposedly choosing more than one IDENTITY column?

Re: How to Create MS SQL Server Table With PK and Insert into it

_AnonymousUser — Tue, 04 May 2010 20:48:02 GMT

Here's another funky thing I noticed. If in the schema editor, for my id column, I set DB Type = INT IDENTITY, nothing for Length, 10 for Precision. I went to look in SQL server at the table that Talend created, and that "10" for precision ended up being the Identity Seed for the "id" column in my table in SQL Server. Something is clearly not mapped right.

Re: How to Create MS SQL Server Table With PK and Insert into it

Anonymous — Wed, 05 May 2010 03:28:54 GMT

Hello
Yes, it is impossible to create a table with auto-increment filed on tMysqlOutput component. At the moment, you can use tMysqlTableList to iterate each table and detect the table if exists, if not, use a tMysqlRow to custom the 'create table' sql.
Best regards
Shong

Re: How to Create MS SQL Server Table With PK and Insert into it

_AnonymousUser — Wed, 05 May 2010 13:56:12 GMT

Thanks Shong, I was able to use a tMSSqlRow component to run a SQL command to create the table at the beginning of my script.

Re: How to Create MS SQL Server Table With PK and Insert into it

Anonymous — Sat, 30 Apr 2016 16:46:32 GMT

It is to late to reply, but it may be useful to somebody.
It can be done with any MSSQL component, Just set the parameters as:
Key=True
DB TYPE=INT IDENTITY
LENGTH=1
PRECISION=1

you can always use tCreateTable.