insert statement

Hello,
I'm trying to insert into another table where select clause contains a join
with another table. However the number of columns of the source does not
match with the destination table.
How can i fix this without specifying all columns?Why wouldn't you want to specify the required columns? I strongly
recommend you always list the column names. In the long run it makes
your code much more stable and maintainable.
Your options are basically to list only the required columns or to
create a view containing only the required columns and select from that
instead. Of course you'll then have to list the columns in the view...
Either way, you can save yourself a lot of typing by clicking and
dragging the column name list from the Object Browser in query
analyzer.
David Portas
SQL Server MVP
--|||Hi David,
Of course i don't want to type a lot. But the only difference in the
sourcetable is just one field and that field comes from another database.
Will it work?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
> Why wouldn't you want to specify the required columns? I strongly
> recommend you always list the column names. In the long run it makes
> your code much more stable and maintainable.
> Your options are basically to list only the required columns or to
> create a view containing only the required columns and select from that
> instead. Of course you'll then have to list the columns in the view...
> Either way, you can save yourself a lot of typing by clicking and
> dragging the column name list from the Object Browser in query
> analyzer.
> --
> David Portas
> SQL Server MVP
> --
>|||Will it work? Maybe it will. But I don't believe it is guaranteed to work
unless you specifically list the columns in both lists. Is it worth the
risk? That's up to you but I certainly would not want to put into
production code that I was not sure was correct.
Andrew J. Kelly SQL MVP
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucxlZ$aHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> Hi David,
> Of course i don't want to type a lot. But the only difference in the
> sourcetable is just one field and that field comes from another database.
> Will it work?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
>|||Will what work? If there are fewer columns in the SELECT statement than
in the target table then you have to list the columns in the INSERT
list and/or the SELECT list - SQL Server won't guess which columns you
want to keep.
David Portas
SQL Server MVP
--|||You can copy and paste the column names out of EM (Design Table) and into
Excel, then use Excel to concatenate them into one long comma-separated
string for you. You can then copy and paste that one long string into your
editing program to eliminate some typing.
Thx
Mike C
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucxlZ$aHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> Hi David,
> Of course i don't want to type a lot. But the only difference in the
> sourcetable is just one field and that field comes from another database.
> Will it work?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
>

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

insert or update data records

I'd like to do the following thing with a data flow task

Get all the records from a source (for example customers from a textfile, flat file source) Then check for each record if the customer already exists in a table, for example with a customerID. If not, insert the record in the table (ole db destination), else copy the customer thats already in the table to another table (history table) and update the record with the customer from the textfile.

Is this possible?, and what kind of data flow transformation do I need?

Use a lookup to check if the customer exists. Use error output from the lookup to insert new customers, OLE-DB Destination. Use the normal output from the lookup to feed an OLE-DB Command to do the move and update. That may be easier if wrapped in a stored procedure.