Changing column values on table insert trigger

Hello,

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.

Yes and sorry, this is a newbie question.

On Oracle this work simple:

create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

Thanks
MaikYou can look up SQL Server Books Online to learn how to write a trigger in
t-SQL. You'll need to utilize the inserted/deleted virtual tables to
accomodate multi-row inserts. The update statement will be something along
the lines of:

UPDATE tableX
SET y = B
WHERE EXISTS ( SELECT *
FROM inserted i
WHERE i.key_col = tableX.key_col
AND inserted.x 1000000 ) ;

Here key_col is any column or set of columns that can uniquely identify a
row in tableX.

--
Anith|||Hi,

Quote:

Originally Posted by

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.
>
Yes and sorry, this is a newbie question.
>
On Oracle this work simple:
>
create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

MS SQL Server doesn't have "before action" triggers, so you
have to update the table and use the INSERTED virtual table
to know what records were inserted into the table.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comsql

Change values in SSIS.

Howdy all. In DTS there was a way too change the values of a column during
and export to another table using ActiveX Script:
Source:
Table1
column1
column2
Destination
Table2
column1 (I want this to contain the exact values from Table1.column1)
column2 (I want this to contain the values of Table1.column2 + 10)
Basically you would do this through the mappings, instead of a Copy Column,
you would use an AxtiveX script for that column. How can this be done in
SSIS?
TIA, ChrisR
Hi Chris
You can use a script component as a transform task see
http://www.sqlis.com/default.aspx?307,1
John
"ChrisR" wrote:

> Howdy all. In DTS there was a way too change the values of a column during
> and export to another table using ActiveX Script:
> Source:
> Table1
> column1
> column2
> Destination
> Table2
> column1 (I want this to contain the exact values from Table1.column1)
> column2 (I want this to contain the values of Table1.column2 + 10)
> Basically you would do this through the mappings, instead of a Copy Column,
> you would use an AxtiveX script for that column. How can this be done in
> SSIS?
> TIA, ChrisR
>
>
>
|||Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5FAF7EA7-D0E7-41FB-AF37-B70DA73AE95E@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> You can use a script component as a transform task see
> http://www.sqlis.com/default.aspx?307,1
> John
> "ChrisR" wrote:
during[vbcol=seagreen]
Column,[vbcol=seagreen]

Change values in SSIS.

Howdy all. In DTS there was a way too change the values of a column during
and export to another table using ActiveX Script:
Source:
Table1
column1
column2
Destination
Table2
column1 (I want this to contain the exact values from Table1.column1)
column2 (I want this to contain the values of Table1.column2 + 10)
Basically you would do this through the mappings, instead of a Copy Column,
you would use an AxtiveX script for that column. How can this be done in
SSIS?
TIA, ChrisRThanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5FAF7EA7-D0E7-41FB-AF37-B70DA73AE95E@.microsoft.com...
> Hi Chris
> You can use a script component as a transform task see
> http://www.sqlis.com/default.aspx?307,1
> John
> "ChrisR" wrote:
> > Howdy all. In DTS there was a way too change the values of a column
during
> > and export to another table using ActiveX Script:
> >
> > Source:
> > Table1
> > column1
> > column2
> >
> > Destination
> > Table2
> > column1 (I want this to contain the exact values from Table1.column1)
> > column2 (I want this to contain the values of Table1.column2 + 10)
> >
> > Basically you would do this through the mappings, instead of a Copy
Column,
> > you would use an AxtiveX script for that column. How can this be done in
> > SSIS?
> >
> > TIA, ChrisR
> >
> >
> >
> >
> >

Change values in SSIS.

Howdy all. In DTS there was a way too change the values of a column during
and export to another table using ActiveX Script:
Source:
Table1
column1
column2
Destination
Table2
column1 (I want this to contain the exact values from Table1.column1)
column2 (I want this to contain the values of Table1.column2 + 10)
Basically you would do this through the mappings, instead of a Copy Column,
you would use an AxtiveX script for that column. How can this be done in
SSIS?
TIA, ChrisRHi Chris
You can use a script component as a transform task see
http://www.sqlis.com/default.aspx?307,1
John
"ChrisR" wrote:

> Howdy all. In DTS there was a way too change the values of a column during
> and export to another table using ActiveX Script:
> Source:
> Table1
> column1
> column2
> Destination
> Table2
> column1 (I want this to contain the exact values from Table1.column1)
> column2 (I want this to contain the values of Table1.column2 + 10)
> Basically you would do this through the mappings, instead of a Copy Column
,
> you would use an AxtiveX script for that column. How can this be done in
> SSIS?
> TIA, ChrisR
>
>
>|||Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5FAF7EA7-D0E7-41FB-AF37-B70DA73AE95E@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> You can use a script component as a transform task see
> http://www.sqlis.com/default.aspx?307,1
> John
> "ChrisR" wrote:
>
during[vbcol=seagreen]
Column,[vbcol=seagreen]

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave

|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Change Primary Key Values

Hello,
Is there a way in SQL Server 2000 to reassign primary key
values automatically?
For example, I have a table that has a "Person ID" field
as its primary key that was started with an identity seed
of "98753162". The identity increment is "1".
Now, there are hundreds of records in this table. I want
to reassign these PK values with much shorter numbers,
i.e. subtract "98753160" from all existing values. In
addition, I would need all corresponding foreign key
values in other tables to be updated automatically with
the new values.
Thanks,
Mike1)Distable the foreign key constraint with ALTER TABLE command.
look for alter table command in BOL "alter table... nocheck ..." (look for
nocheck clause) which will stop validation of future inserts or updates to
the column.
2) add a column to primary key table and populate the values of PK to this
column with the statement as
update <table> set new_col=PK_col
3)you can update the existing primary key values with the statement such as
update table set PK_col= pk_col - 98753160 --required subtraction.(make sure
primary key constraint is not violated)
4) once this is done you will have to fire an update statement on the FK
tables to update the new FK values replacing the old one.
--
-Vishal
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:038e01c3555a$95864c50$a601280a@.phx.gbl...
> Hello,
> Is there a way in SQL Server 2000 to reassign primary key
> values automatically?
> For example, I have a table that has a "Person ID" field
> as its primary key that was started with an identity seed
> of "98753162". The identity increment is "1".
> Now, there are hundreds of records in this table. I want
> to reassign these PK values with much shorter numbers,
> i.e. subtract "98753160" from all existing values. In
> addition, I would need all corresponding foreign key
> values in other tables to be updated automatically with
> the new values.
> Thanks,
> Mike

Change NULL values to default in SELECT statement

I have a stored procedure with a SELECT statement, that retrieves 1 row.

SELECT name FROM tblNames WHERE nameID = "1"

I want all the NULL values in that row to be change in some default values.

How do I do this?

You can use the IsNull Statement

SELECT isNull(name,'someDefaultValue') FROM tblNames WHERE nameID = "1"

|||

Perfect, perfect, this works great, so simple, but effective.

Now I can go celebrating Christmas.....thx.

|||

Next problem:

I call the stored procedure in the Data Access Layer via a typed dataset.

My stored procedure SELECTs 11 columns. The execute test in the stored procedure works perfect.

But when I preview the data in the DAL.xsd, I see 22 columns, with the first 11 columns having the right name, but no values and the second 11 columns having wrong names, but the right values.

What goes wrong?

|||

You will have to rename the derived columns in your select statement...

SELECT ISNULL(FirstName,'No Name') [FirstName], ISNULL(LastName,'No Last Name') [LastName]

It also sounds like you may be using SELECT *, remove the star so you don't end up with duplicate column names