Table Merge -- Trying to Avoid Cursors

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Merge -- Trying to Avoid Cursors

Author

Topic

parkerjm
Starting Member

4 Posts

Posted - 2010-06-09 : 10:21:40

I have two tables of identical structures and I want to merge the data into a single table. However, the primary key of the table might be duplicated, so I want to just toss out one or the other (I don't care which one)

So, say I have two tables T1 and T2 with structure int, varchar(100)

T1
-----------
1, hello
2, test
3, hiiii

T2
-----------
1, nope
4, yep
5, sure

I want a query to return:
-----------
1, hello (or nope, don't care!)
2, test
3, hiiii
4, yep
5, sure

My real tables have more columns than that, but only a single column PK. Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 10:25:28

select columns from table1
union all
select columns from table2 as t2 where not exists(select * from table1 where col1=t2.col1)

Madhivanan

Failing to plan is Planning to fail

parkerjm
Starting Member

4 Posts

Posted - 2010-06-09 : 10:27:26

That worked perfectly.

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 10:28:43

quote:
Originally posted by parkerjm

That worked perfectly.

Thanks!

You are welcome

Madhivanan

Failing to plan is Planning to fail

parkerjm
Starting Member

4 Posts

Posted - 2010-06-09 : 10:43:28

Ah, unfortunately, it looks a little more complicated than that....

My problem is that instead of just T1 or T2 there are many T tables, and I can't know how many and I can't know what they are called outright. Their names are derived from a base table T, so their names are like T + "_" + unique_id. The unique ID is basically a field pulled out of a different table (select name from node where node_interface="blah").

I want to merge all T_x tables into T without any duplicates, tossing out duplicate PK data at random. At the time I do the merge, T will be empty initially.

I had an idea that involved deriving all T_x table names and then using a cursor to loop over them all, doing insert into T (T union T_x) for all x, but that will of course result in PK violations. I could add a WHERE clause into the insert maybe?

Is there any way to do that without cursors?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 11:27:09

Look at using the IGNORE_DUP_KEY option for the PK on your merge table.

parkerjm
Starting Member

4 Posts

Posted - 2010-06-09 : 13:37:07

To your dismay, check out what I ended up with. (specics have been blahed out)

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('backing_table_blah'))
BEGIN
	DECLARE @sep_table VARCHAR(100)
	DECLARE sep_tables_cursor CURSOR FOR SELECT 'backing_table_blah' + '_' + node FROM node WHERE node_interface = 'blah'
	OPEN sep_tables_cursor
	FETCH NEXT FROM sep_tables_cursor INTO @sep_table
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(@sep_table))
		BEGIN
			DECLARE	@c1 VARCHAR(100)
			DECLARE @c2 VARCHAR(100)
			DECLARE @c3 VARCHAR(100)
			DECLARE @c4 VARCHAR(100)
			EXEC('DECLARE table_items_cursor CURSOR FOR SELECT * FROM ' + @sep_table)
			OPEN table_items_cursor
			FETCH NEXT FROM table_items_cursor INTO @c1, @c2, @c3, @c4
			WHILE (@@FETCH_STATUS = 0)
			BEGIN
				IF NOT EXISTS (SELECT c1 FROM backing_table_blah WHERE c1 = @c1)
				BEGIN
					INSERT INTO backing_table_blah VALUES (@c1, @c2, @c3, @c4)
				END
				FETCH NEXT FROM table_items_cursor INTO @c1, @c2, @c3, @c4
			END
			CLOSE table_items_cursor
			DEALLOCATE table_items_cursor
		END
		FETCH NEXT FROM sep_tables_cursor INTO @sep_table
	END
	CLOSE sep_tables_cursor
	DEALLOCATE sep_tables_cursor
END

hahah Let me have it, guys.

Oh well, it's just a run-once script.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources