SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inserting from one table to another
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  14:47:19  Show Profile  Send dzabor an AOL message  Reply with Quote
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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  15:07:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think you can replace all code above with this little puppie
INSERT	cana_01dnn.gen_tables
SELECT	t1.Code,
	t1.DESCRIPTION
FROM	dbo.cana_01imis.gen_tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn.gen_tables AS t2 WHERE t2.Code = t1.Code)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  16:17:45  Show Profile  Send dzabor an AOL message  Reply with Quote
That makes sense, but I am still having trouble with the syntax. It is not recognizing the table names. Which one should I use in the use statement. I found the name of the tables I am inserting to and from are different. code = category and description = categoryname

INSERT cana_01dnn.EventsCategory as t1
SELECT t1.Code,
t1.DESCRIPTION
FROM cana_01imis.gen_tables AS t2
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory WHERE t2.Code = t1.Code)

use cana_01dnn

INSERT cana_01dnn.EventsCategory as t1
SELECT t1.Code,
t1.DESCRIPTION
FROM cana_01imis.gen_tables AS t2
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory WHERE t2.Code = t1.Code)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  16:21:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have NOT copied and pasted the suggestion I made. You have altered the syntax to a flawed one.
Well, it is your code. If you do not want help and learn from it, good luck.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  16:24:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
INSERT	cana_01dnn.EventsCategory
SELECT	t1.Code,
	t1.DESCRIPTION
FROM	cana_01imis.gen_tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory AS t2 WHERE t2.Code = t1.Code)
Now study and learn.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  16:37:08  Show Profile  Send dzabor an AOL message  Reply with Quote
I apologize. I ran this:

INSERT cana_01dnn.EventsCategory
SELECT t1.Code,
t1.DESCRIPTION
FROM cana_01imis.gen_tables AS t1
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory AS t2 WHERE t2.Code = t1.Code)

and the following error returned:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'cana_01dnn.EventsCategory'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'cana_01imis.gen_tables'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'cana_01dnn.EventsCategory'.
It does not like me using the table name in front of the fields.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  16:46:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is no owner for the tables. Try this
INSERT	cana_01dnn..EventsCategory
SELECT	t1.Code,
	t1.DESCRIPTION
FROM	cana_01imis..gen_tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn..EventsCategory AS t2 WHERE t2.Code = t1.Code)



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

Edited by - SwePeso on 11/09/2007 16:48:31
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  16:53:13  Show Profile  Send dzabor an AOL message  Reply with Quote
What does the .. stand for?

I triedf your script - we are almost there. The only error coming up now is the following:

Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Thanks for all of your help with this!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  16:56:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
INSERT	cana_01dnn.dbo.EventsCategory
	(
		Code,
		Description
	)
SELECT	t1.Code,
	t1.DESCRIPTION
FROM	cana_01imis.dbo.gen_tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.Code = t1.Code)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  17:01:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Show your mentors the above suggested code.
Then come back here and tell us what your mentors said about it.
Please.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  17:05:48  Show Profile  Send dzabor an AOL message  Reply with Quote
It must work because the only error I get is the following

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'EventsCategory' when IDENTITY_INSERT is set to OFF.

I will have to set it to ON.

I showed this code and my mentor said - "sure, that will work".

Thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  17:09:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is code an identity column in the target table?
If so, the original code will not work either...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  17:16:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON

INSERT	cana_01dnn.dbo.EventsCategory
	(
		Code,
		Description
	)
SELECT	t1.Code,
	t1.Description
FROM	cana_01imis.dbo.Gen_Tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.Code = t1.Code)

SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory OFF



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  17:17:03  Show Profile  Send dzabor an AOL message  Reply with Quote
yes - can't i just use the code:

SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON

Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  17:17:39  Show Profile  Send dzabor an AOL message  Reply with Quote
Wow - you respond fast. Thanks!
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/09/2007 :  17:28:46  Show Profile  Send dzabor an AOL message  Reply with Quote
After all that and it looks like I cannot even insert into that field. it is the wrong type of variable. I am researching, but here is the error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'CASH' to a column of data type int.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/09/2007 :  17:41:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here is how you insert only numeric Codes
SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON

INSERT	cana_01dnn.dbo.EventsCategory
	(
		Code,
		Description
	)
SELECT	t1.Code,
	t1.Description
FROM	cana_01imis.dbo.Gen_Tables AS t1
WHERE	NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.Code = t1.Code)
	AND t1.Code NOT LIKE '%[^0-9]%'

SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory OFF



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/10/2007 :  12:13:40  Show Profile  Send dzabor an AOL message  Reply with Quote
I need to insert this to the script. The t2.category is int and the t1.code is varchar

select convert(int, t1.code)


SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON

INSERT cana_01dnn.dbo.EventsCategory
(
portalid,
category,
categoryname
)
SELECT t1.table_name,
t1.Code,
t1.Description
FROM cana_01imis.dbo.Gen_Tables AS t1
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.category = t1.Code)
and t1.table_name = 'MEET_DNN'

SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory OFF
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/10/2007 :  13:32:03  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON

INSERT cana_01dnn.dbo.EventsCategory
(
portalid,
category,
categoryname
)
SELECT t1.table_name,
t1.Code,
t1.Description
FROM cana_01imis.dbo.Gen_Tables AS t1
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.category = Convert(int,t1.Code))
and t1.table_name = 'MEET_DNN'

SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory OFF

You already know the answer, have a little faith in your ability to attempt a solution. I would say convert it in the join, but I am sure there is a better way that I don't know off hand.
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/11/2007 :  20:46:09  Show Profile  Send dzabor an AOL message  Reply with Quote
Thanks - that worked!
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 11/11/2007 :  21:56:16  Show Profile  Send dzabor an AOL message  Reply with Quote
I spoke too fast and the developer changed his mind about what fields to populate. Neither of these field are the primary, but portalid is an int and the table_name is a var char. I have placed the convert statement everywhere I can think of and it keeps coming up syntax error converting. When I double click the error to see where it is coming from it highlights my INSERT line.

INSERT cana_01dnn.dbo.EventsCategory
( portalid,
categoryname
)
SELECT t1.table_name,t1.Description
FROM cana_01imis.dbo.Gen_Tables AS t1
WHERE NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.portalid = convert(int,t1.table_name))
and t1.table_name = 'MEET_DNN'
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000