using column notaition in SQL
- Verified User
- Posts: 14
- Joined: Tue May 26, 2009 8:54 am
SPACE(4 - LENGTH(TRIM(CCSCO1)) ) || TRIM(CCSCO1)
This doesn't seem to work in GA as the markers are applied sequentially, and I cant seem make it work. I have been able to work around this by first updating the table and then applying an update query, but when in production it will hitting about 200,000 to 300, 000 rows and for performance reasons I'd like to do it in a single pass.
-
Support_Sai
Below is a sample project to demonstrate how parameter mapping can be done in the SQL task:
<project name="SQL Task Parameter Mapping" mainModule="Main" version="1.0">
<description></description>
<module name="Main">
<readCSV inputFile="C:\Users\spullabhotla\Desktop\persons.csv" outputRowSetVariable="data" />
<sql label="Connect to DB" resourceId="Production">
<query inputRowSetVariable="${data}">
<statement>insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)</statement>
<param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
</query>
</sql>
</module>
</project>
1001,John,Doe,1 Main St.,Omaha,NE,68135
1002,John,Smith,3820 Dodge St.,Omaha,NE,68125
1003,Cindy,Clark,7000 S 100th PLZ,Omaha,NE,68128
Field Name Type/Size
PERSON_ID INTEGER
FNAME CHAR(30)
LNAME CHAR(30)
NAME VARCHAR(61)
ADDRESS VARCHAR(255)
ZIP CHAR(10)
The insert Query used in the project is -
insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)
The above query populates the name field by concatenating the first name, followed by a white space and last name. Then it also populates the normal first name and last name fields. This is done by defining a custom parameter mapping.
<param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
- Map 1st parameter (?) in the query from 1st column of the input rowset.
- Map 2nd parameter (?) in the query from 2nd column of the input rowset.
- Map 3rd parameter (?) in the query from 3rd column of the input rowset.
- Map 4th parameter (?) in the query again from 2nd column of the input rowset.
- Map 5th parameter (?) in the query again from 3rd column of the input rowset.
- Map 6th parameter (?) in the query from 4th column of the input rowset.
- Map 7th parameter (?) in the query from 5th column of the input rowset.
- Verified User
- Posts: 14
- Joined: Tue May 26, 2009 8:54 am