473,545 Members | 2,688 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert Records From Table1 that do no exist in Table2

Hi guys,

i have a little problem here.

im attempting to write a stored procedure that compares two tables of
the same data structure and adds (inserts) extra records that exist in
table1 to table2.

My problem is that i dont have a unique identifier between the tables.

i think someone said that i needed to build up a key

any ideas greatly appreciated ??

C

Jul 23 '05 #1
1 1539
Every table should have a key. If you don't have one then I suggest you
fix that first - clean up your data if necessary and add a UNIQUE /
PRIMARY KEY constraint.

(You mentioned "unique identifier" by which I infer that you really
meant "unique key". SQL Server has a datatype called UNIQUEIDENTIFIE R
but it's just a datatype - it isn't required for a key.)

Once you have a key (key_col in this example) you can do an INSERT like
this:

INSERT INTO Table2 (key_col, col1, col2, ...)
SELECT T1.key_col, T1.col1, T1.col2, ...
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.key_col = T2.key_col
WHERE T2.key_col IS NULL

Hopefully it's obvious that this will extend to any number of non-NULL
columns to make a unique key, so if you haven't figured out a key yet
it may help just to join on everything:

INSERT INTO Table2 (col1, col2, col3, ...)
SELECT DISTINCT T1.key_col, T1.col1, T1.col2, T1.col3, ...
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND ... etc
WHERE T2.col1 IS NULL

Be careful about columns with NULLs however, you probably won't get the
result you expect in that case.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
6914
by: soni29 | last post by:
hi, i have a small question regarding sql, there are two tables that i need to work with on this, one has fields like: Table1: (id, name, street, city, zip, phone, fax, etc...) about 20 more columns Table2: name what i need help with is that table2 contains about 200 distinct names that i need to insert into table1, i'm using sql server,...
5
3219
by: ndn_24_7 | last post by:
Hello all, I'm trying combine 2 tables data into a single table. My tables look like this Table1 ID int 4 filename nvarchar 25 incident nvarchar 50 dreport nvarchar 10
1
1979
by: seahorse123 | last post by:
I want to insert records to the table1 from variable and table2, for example: "insert into table1 (column1,column2,column3) from ('abcd',select a,b from table2)" where insert variable 'abcd' into column1, and insert a,b from table2 into column2,column3, how to write the correct insert statement? thanks.
16
16979
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then...
2
2997
by: Max | last post by:
Hi, I have the following problem. I want to insert records into one table using the combination of two other tables. Here is a simplified example: Let's say the first table has a column with RaceCode (which might have values such as 1,3,5,7, etc), the second table is like a key for this RaceCode so it has two columns (1st one is 1,3,5,7, etc...
7
3772
by: Nicolae Fieraru | last post by:
I have two tables, they contain: Table1: ID1, Name1, Address1, Purchase1 Table2: ID2, Name2, Address2, Purchase2 I need a query which creates Table3 with content from Table1 and Table2. The records in Table3 have to contain all distinct records from Table1 and Table2 (records where Name2 and Address2 do not already exist in Table3) and ...
4
5467
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why this would be happening, we would appreciate feedback. We have tested on 7.3.4, 7.3.6 and 7.4.1 and all exhibit the same behavior. Test case one...
0
1421
by: Ohad Weiss | last post by:
Hi all, I've once asked about that topic. but didn't get an answer. I have a dataset based on 4 tables, which have relation between them. The main table presented to the user on textboxes placed on the form, and the rest of the tables presented on textboxes placed on tabpages (tabpage for each table). Here is the code I use:
6
24697
markrawlingson
by: markrawlingson | last post by:
Hopefully someone can help me out with this, it's driving me nuts... I have two tables - We'll call them table1 and table2. Table1 holds event information, and table2 holds user registration data pertaining to each event in table1. So for each record in Table1 there could be hundreds of records pertaining to that record in Table2 - I am trying...
0
7499
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7432
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7689
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7456
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6022
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5359
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3490
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1919
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.