Insert Into --new records only
-
Hi, I want to run a job in SQL 2005 Developer edition, where I transfer data from one table in a db into a table with the same schema on a second db every half hour. But I only want to transfer data that's new data, so I want to keep all the data that is there but only add new records that have changed in the last half hour based on the PK. For instance:
select * into tbl2
from tbl1
--not sure what to write after this...
where tbl1.pk doesn't exist in tbl1
I would appreciate your help as my sql coding skills are quite amateurish. Thank you.
-
...
Where not exists
(select pk
from tbl2
where tbl2.pk = tbl1.pk)
-
You had it about right. Use a "not exists" clause. Here is an example presupossing two databases (db1 and db2) both with a tabled named t1 which have two columns (col1, col2) where col1 is of type identity and the primary key.
set identity_insert db2..t1 on
insert into db2..t1 (col1,col2)
select col1,col2
from db1..t1 a
where not exists (select 1 from db2..t1 where col1 = a.col1)
set identity_insert db2..t1 off
James.
-
Oops! I see Mark beat me to it. That's what I get for taking a bathroom break before finishing my post
-
Hi guys, thanks so much for your help, your suggestions make sense but I am receiving the following error message.
The multi-part identifier "db2.tbl2.workitemid" could not be bound.
Part of the problem may be, and I should explain a little further. I want to move records from a working db to an archive db, so the schema's of both tables are the same but they don't reside in the same db, and they do not have an explicit relationship.
-
Shouldn't matter as long as you have permissions on both databases and both tables. Make sure you referencing the objects fully, haven't misspelled any of the names:
DatabaseName.ObjectOwner.TableName.ColumnName
Usually when you see that error you have something wrong in the reference. From your post it looks like you forgot the objectowner. You can short cut that part with two periods together, example "DatabaseName..TableName"
If you still can't figure it out post the ACTUAL code you are using and we might spot the problem.
James.
-
Hi James & Mark,
here is the actual code with the error message:
Code:
use
occ_archives
insert into asqcrr.occ_archives.dbo.hvcfids
select
* from asqcrr.occ_archives.dbo.hvcfids as dest
Where
not exists
(
select workitemid
from
asqcrr.occ_archives.dbo.hvcfids as source
where
source.workitemid = occ.dbo.hvcfids.workitemid)
Error Message:
The multi-part identifier "occ.dbo.hvcfids.workitemid" could not be bound.
PS:
Now I'm getting more confuse, do I have the Select * from statement as dest in the correct order? should I be selecting * from the source table?
-
Way too much info
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
use occ_archives
insert into asqcrr.occ_archives.dbo.hvcfids
select * from asqcrr.occ_archives.dbo.hvcfids as dest
Where not exists
(select workitemid
from asqcrr.occ_archives.dbo.hvcfids as source
where source.workitemid = dest.workitemid)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That worked Jeff! You're my personal hero today.
Thanks,
have a good weekend.
-
Hmm, are we talking about databases on two different physical servers or two different instances of SQL Server?
If so are they linked?
It looks like your Source and Destination tables are the same table!
insert into DestinationTable --I guess this equates to your asqcrr.occ_archives.dbo.hvcfids
select * from SourceTable --This is the source table which in your codes doesn't seem to be different from the destination
where not exits
(
select 1 --doesn't matter what is in the select statement here
from DestinationTable --This is the DESTINATION table and should be exactly same as in the insert statement above
where DestinationTable.PrimaryKey = SourceTable.PrimaryKey)
)
Now try taking the above SQL and simply substitute your actual table names and see if you can get it working.
James.
-
Dang, I'm playing second fiddle to everyone today.
Guess it's time to call it a day and head for the showers. I'm going to have to practice getting faster at this stuff. -
Heh, too much "potty" time, James?
(just kidding) Me? The shower can wait, I'm headed for a beer! --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You too... Thanks, Marcus...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply