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_01imisDECLARE @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.DESCRIPTIONWHILE @@FETCH_STATUS = 0BEGIN --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 hereINSERT INTO cana_01dnn.gen_tablesVALUES (Gen_Tables.Code,Gen_Tables.DESCRIPTION) FETCH NEXT FROM cur INTO @Gen_Tables.Code,@Gen_Tables.CodeENDCLOSE curDEALLOCATE 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 puppieINSERT cana_01dnn.gen_tablesSELECT t1.Code, t1.DESCRIPTIONFROM dbo.cana_01imis.gen_tables AS t1WHERE 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
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 = categorynameINSERT cana_01dnn.EventsCategory as t1SELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis.gen_tables AS t2WHERE NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory WHERE t2.Code = t1.Code)use cana_01dnnINSERT cana_01dnn.EventsCategory as t1SELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis.gen_tables AS t2WHERE NOT EXISTS (SELECT * FROM cana_01dnn.EventsCategory WHERE t2.Code = t1.Code) |
|
|
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 16:24:36
|
[code]INSERT cana_01dnn.EventsCategorySELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis.gen_tables AS t1WHERE 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" |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2007-11-09 : 16:37:08
|
I apologize. I ran this: INSERT cana_01dnn.EventsCategorySELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis.gen_tables AS t1WHERE 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 1Invalid object name 'cana_01dnn.EventsCategory'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'cana_01imis.gen_tables'.Server: Msg 208, Level 16, State 1, Line 1Invalid 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
30421 Posts |
Posted - 2007-11-09 : 16:46:02
|
There is no owner for the tables. Try thisINSERT cana_01dnn..EventsCategorySELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis..gen_tables AS t1WHERE NOT EXISTS (SELECT * FROM cana_01dnn..EventsCategory AS t2 WHERE t2.Code = t1.Code) E 12°55'05.25"N 56°04'39.16" |
|
|
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 1Insert 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
30421 Posts |
Posted - 2007-11-09 : 16:56:19
|
[code]INSERT cana_01dnn.dbo.EventsCategory ( Code, Description )SELECT t1.Code, t1.DESCRIPTIONFROM cana_01imis.dbo.gen_tables AS t1WHERE 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" |
|
|
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" |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2007-11-09 : 17:05:48
|
It must work because the only error I get is the followingServer: Msg 544, Level 16, State 1, Line 1Cannot 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
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 17:16:52
|
[code]SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ONINSERT cana_01dnn.dbo.EventsCategory ( Code, Description )SELECT t1.Code, t1.DescriptionFROM cana_01imis.dbo.Gen_Tables AS t1WHERE 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" |
|
|
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 |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2007-11-09 : 17:17:39
|
Wow - you respond fast. Thanks! |
|
|
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 3Syntax error converting the varchar value 'CASH' to a column of data type int. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 17:41:58
|
Here is how you insert only numeric CodesSET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ONINSERT cana_01dnn.dbo.EventsCategory ( Code, Description )SELECT t1.Code, t1.DescriptionFROM cana_01imis.dbo.Gen_Tables AS t1WHERE 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
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 varcharselect convert(int, t1.code)SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ONINSERT cana_01dnn.dbo.EventsCategory ( portalid, category, categoryname )SELECT t1.table_name, t1.Code, t1.DescriptionFROM cana_01imis.dbo.Gen_Tables AS t1WHERE 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
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-10 : 13:32:03
|
SET IDENTITY_INSERT cana_01dnn.dbo.EventsCategory ONINSERT cana_01dnn.dbo.EventsCategory(portalid,category,categoryname)SELECT t1.table_name,t1.Code,t1.DescriptionFROM cana_01imis.dbo.Gen_Tables AS t1WHERE 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 OFFYou 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
138 Posts |
Posted - 2007-11-11 : 20:46:09
|
Thanks - that worked! |
|
|
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.DescriptionFROM cana_01imis.dbo.Gen_Tables AS t1WHERE NOT EXISTS (SELECT * FROM cana_01dnn.dbo.EventsCategory AS t2 WHERE t2.portalid = convert(int,t1.table_name)) and t1.table_name = 'MEET_DNN' |
|
|
Next Page
|