Dts package that imports difference

Hi,
I have to server and two different database on them. I want to import one
table from database1 to database2. But I want to import only difference
between two table.I want to also keep identity column value. I created a dts
package but I don't know how can I put a condition like "where myid not
in(Select myid from database2.table)" and where can I put Set
IDENTITY_INSERT mytable ON?
Thanks in advance,Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>|||I don't think you can connect from "Execute SQL task" to both server. You
need to select connection(server) first. Am I wrong?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
> Assuming you are using an Execute SQL task, you can use a script like the
> one below:
>
> SET IDENTITY_INSERT Database2.dbo.MyTable ON
> GO
> INSERT INTO Database2.dbo.MyTable
> (
> ID,
> MyData
> )
> SELECT
> ID,
> MyData
> FROM Database1.dbo.MyTable AS t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM Database2.dbo.MyTable AS t2
> WHERE t2.ID = t1.ID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "italic" <hugur@.hotmail.com> wrote in message
> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import one
>> table from database1 to database2. But I want to import only difference
>> between two table.I want to also keep identity column value. I created a
>> dts package but I don't know how can I put a condition like "where myid
>> not in(Select myid from database2.table)" and where can I put Set
>> IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>|||in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||Hi
"italic" wrote:
> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John|||Linked Server is not allowed. I can try the second way.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE7E5676-71E5-4202-9ACB-BFEC91B180C8@.microsoft.com...
> Hi
> "italic" wrote:
>> I don't think you can connect from "Execute SQL task" to both server. You
>> need to select connection(server) first. Am I wrong?
>> Thanks
> You can create a linked server and reference that in your query, or as you
> have a second connection to the destination server you could use a query
> (task) to retrieve the highest identity from the destination server then
> use
> that in a global variable as the start of the information retrieved from
> the
> second server.
> See http://www.sqldts.com/280.aspx
> http://www.sqldts.com/205.aspx
> The second method would not necessarily be transactionally consistent,
> therefore you should not be changing the destination table when this is
> running.
>
> John|||Thanks for reply.
"Jeje" <willgart@.hotmail.com> wrote in message
news:ORGk$P8kHHA.5048@.TK2MSFTNGP04.phx.gbl...
> in DTS,
> first you have to get the lastet ID (select max(id) from destination)
> then you have to dynamically create the select statement (Select * from
> source where id > maxid)
> (or you have to use a parametrized query where the parameter will be the
> ID)
> and finally load the data.
>
>
> "italic" <hugur@.hotmail.com> wrote in message
> news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>>I don't think you can connect from "Execute SQL task" to both server. You
>>need to select connection(server) first. Am I wrong?
>> Thanks
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like
>> the one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>>|||Hi
"italic" wrote:
> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John|||I mean, company db admin don't want linked server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0C51877A-3573-4795-83F9-017AAA43B870@.microsoft.com...
> Hi
> "italic" wrote:
>> Linked Server is not allowed. I can try the second way.
>> Thanks
> You would need a 4 part name! What error do you get?
> John