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
 General SQL Server Forums
 New to SQL Server Programming
 Inserting from one table to another

Author  Topic 

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 14:47:19
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

30421 Posts

Posted - 2007-11-09 : 15:07:46
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

138 Posts

Posted - 2007-11-09 : 16:17:45
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

30421 Posts

Posted - 2007-11-09 : 16:21:01
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

30421 Posts

Posted - 2007-11-09 : 16:24:36
[code]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)[/code]Now study and learn.



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

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 16:37:08
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

30421 Posts

Posted - 2007-11-09 : 16:46:02
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"
Go to Top of Page

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 16:53:13
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

30421 Posts

Posted - 2007-11-09 : 16:56:19
[code]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)[/code]


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 17:01:42
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

138 Posts

Posted - 2007-11-09 : 17:05:48
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

30421 Posts

Posted - 2007-11-09 : 17:09:45
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

30421 Posts

Posted - 2007-11-09 : 17:16:52
[code]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[/code]


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

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 17:17:03
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

138 Posts

Posted - 2007-11-09 : 17:17:39
Wow - you respond fast. Thanks!
Go to Top of Page

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-09 : 17:28:46
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

30421 Posts

Posted - 2007-11-09 : 17:41:58
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

138 Posts

Posted - 2007-11-10 : 12:13:40
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
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-10 : 13:32:03
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

138 Posts

Posted - 2007-11-11 : 20:46:09
Thanks - that worked!
Go to Top of Page

dzabor
Posting Yak Master

138 Posts

Posted - 2007-11-11 : 21:56:16
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
    Next Page

- Advertisement -