| Author |
 Topic  |
|
dzabor
Posting Yak Master
USA
126 Posts |
 Posted - 11/09/2007 : 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
Sweden
29138 Posts |
Posted - 11/09/2007 : 15:07:46
|
I think you can replace all code above with this little puppieINSERT 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" |
 |
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 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) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 16:24:36
|
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" |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 16:46:02
|
There is no owner for the tables. Try thisINSERT 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 |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 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!!! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 16:56:19
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 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" |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 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! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 17:16:52
|
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" |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 17:17:03
|
yes - can't i just use the code:
SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ON
|
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 17:17:39
|
| Wow - you respond fast. Thanks! |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/09/2007 : 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/09/2007 : 17:41:58
|
Here is how you insert only numeric CodesSET 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" |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/10/2007 : 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 |
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/10/2007 : 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. |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/11/2007 : 20:46:09
|
| Thanks - that worked! |
|
|
|
dzabor
Posting Yak Master
USA
126 Posts |
Posted - 11/11/2007 : 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' |
|
|
|
Topic |
|
Inserting from one table to another
