Insert statement
-
Hi,
I have a table (TableA) as follows:
ColumnA ColumnB
----------------------
Value01 R01
Value01 R02
Value02 R04
I have another table as follows (TableB):
ColumnA ColumnB
----------------------
Value02 R01
Value01 R03
Value01 R01
I want to insert into TableA all the values from TableB that do not currently exist in TableA. I want to do this without using a cursor.
I dont want to do this with an "IF EXISTS" either as this is slow.
I tried something like the following:
INSERT INTO [TableA]([ColumnA], [ColumnB])
SELECT [ColumnA], [ColumnB]
FROM [TableB] LEFT JOIN [TableA] ON
[TableB].[ColumnA] = [TableA].[ColumnA]
AND [TableB].[ColumnB] = [TableA].[ColumnB]
WHERE [TableA].[ColumnB] IS NULL
but is doesnt seem to work.
Any ideas?
Thanks in advance.
Kabir
-
I just changed your insert query a little, but give it a try:
INSERT INTO [TableA](
[ColumnA],
[ColumnB]
 
SELECT
b.[ColumnA],
b.[ColumnB]
FROM
[TableB] b
LEFT OUTER JOIN [TableA] a
ON (b.[ColumnA] = a.[ColumnA]
AND b.[ColumnB] = a.[ColumnB]
WHERE
a.[ColumnA] IS NULL
-
If the insert is too slow, check that both tables are indexed properly and that indexes are not fragmented (see DBCC INDEXDEFRAG and DBCC DBREINDEX if you don't know what I mean). You may want to test performance with separate indexes on columns A and B, and composite index on both, to see what works better.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply