inserting into one table from another

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)

inserting into one table from another

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 15:00:47

I am trying to insert data into a table from another where not exists. This will be a table that writes from one table to another if there has been an update from the original table. Both tables have the same field and table names. This is what I have so far:

USE cana_01imis

DECLARE @Gen_Tables.Code AS VARCHAR(60)
DECLARE @Gen_Tables.DESCRIPTION AS VARCHAR(255)

DECLARE cur CURSOR FAST_FORWARD FOR
SELECT Gen_Tables.Code, Gen_Tables.DESCRIPTION
FROM dbo.cana_01imis.gen_tables
WHERE NOT IN (SELECT Gen_Tables.Code FROM Gen_Tables)

FETCH NEXT FROM cur INTO @Gen_Tables.Code, @Gen_Tables.DESCRIPTION
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT Gen_Tables.Code, Gen_Tables.DESCRIPTION from gen_tables

DECLARE @DNNGen_Tables AS VARCHAR(60)
SET @DNNGen_Tables.code = @Gen_Tables.Code

DECLARE @DNNGen_Tables.Code DESCRIPTION AS VARCHAR(255)
SET @DNNGen_Tables.Code DESCRIPTION = @Gen_Tables.description

-- then your insert statement goes here
INSERT INTO cana_01dnn.gen_tables
VALUES (Gen_Tables.Code,Gen_Tables.DESCRIPTION)

FETCH NEXT FROM cur INTO @Gen_Tables.Code,@Gen_Tables.Code
END

CLOSE cur
DEALLOCATE cur

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-09 : 15:51:52

With the cursor option you are doing the operation row by row. To do the same insert (insert from TableA where not exists in TableB) use the following "set based" script:


insert into cana_01dnn.gen_tables (Code, [Description])
	select	a.Code, 
		a.DESCRIPTION
	from	dbo.cana_01imis.gen_tables a
	left
	join	cana_01dnn.gen_tables b on
		a.Code = b.Code
	where	b.Code is null

Ill assume that Code is the primary key of both tables. You can comment out the INSERT statement, and only run the SELECT portion to see what resultset would get inserted.

Please post back if you need additional info.

Nathan Skerl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:06:50

Cross post here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92399

E 12°55'05.25"
N 56°04'39.16"

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-09 : 16:42:40

I don't understand... why? why people are writing so many complicated lines of code with @, declare, FETCH, WHILE while it can be done by just one SQL stetement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:53:31

Because they are procedural programmers from the beginning and haven't made the transition to set-based thinking?

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 16:57:07

I think it is because our mentors are trying to over complicate things to make themselves feel smarter. The code you gave me not only was easier, but easier to understand. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:59:22

Show your mentors the code on the other topic linked above.
Then come back here and tell us what your mentors said.
Please.

E 12°55'05.25"
N 56°04'39.16"

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-09 : 17:00:49

yup, i agree w/ Peso. I see it often... programmers with a top-to-bottom procedural background gravitate toward the cursor. It makes sense, its probably difficult to make the transition to set based mindset.

Venn diagrams are your friend :)

Nathan Skerl

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 17:06:34

My mentor said "Yep, that will work"

Thanks

doco
Yak Posting Veteran

77 Posts

Posted - 2007-11-10 : 09:42:24

nathans;