Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2005-10-25 : 09:00:32
|
| Hi,I've got two tables, we'll call them A and B. All the records that are in B are also in table A, but table A also has a lot of records that are not in B. To further confuse matters, B has a proper primary key but A does not.What I want to do is write a script which isolates all the records in A which are not in B and place them in a seperate table (which we'll call C). The records that are in both B and A are identical in both tables and there are two or three ntext fields which could be used to check whether any given record is identical across both A and B.Does this make sense, and is it doable without a proper key in A? If so, how?Cheers,Matt |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-25 : 09:04:10
|
| Create a table C with the columns you wantThenInsert into C(columns) Select Columns from A T1 where not exists(Select * from B where col1=T.col1 and col2=T.col2 and col3=T.col3)MadhivananFailing to plan is Planning to fail |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2005-10-25 : 09:11:10
|
| Hi,Sorry to be unclear in the first post, but I orginially said they were nvarchar data types when in fact they're ntext. Sql seems unwilling to let me compare ntext data .. is there any way round this?Cheers,Matt |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-25 : 09:13:15
|
| You can convert it into varchar type and compareconvert(varchar(8000),col1)=convert(varchar(8000),T1.col1)MadhivananFailing to plan is Planning to fail |
|
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2005-10-25 : 09:16:53
|
| That's great thanks.I never knew you could do on-the-fly data type conversions like that, so thanks also for broadening the narrow horizons of my SQL knowledge :) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-25 : 09:30:24
|
>>I never knew you could do on-the-fly data type conversions like that,Better to avoid comparing those columns  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Table Comparison