Results 1 to 7 of 7

Thread: Insert into

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Insert into

    I am trying to synchronize two tables that are in different databases. If the customer does not exist in the orgloc table but it does in the r2l_test table, then it will get inserted into the orgloc table. This whole function works fine. How can I insert a customer that already exsists in the orgloc table but it has a different loccode? This record would be coming from the r2l_test table. That means that custcode and loccode would be the key. How can I go about modifying the code below to do this. I haven't been able to figure out yet.

    insert into [r2lprospector].[dbo].[orgloc](custcode, loccode, custshiptoname)
    select custcode, loccode, custshiptoname
    from [cimpro1].[dbo].[r2l_test]where custcode not in
    (select custcode from [r2lprospector].[dbo].[orgloc])

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not sure I follow

    Code:
    INSERT INTO [r2lprospector].[dbo].[orgloc](custcode, loccode, custshiptoname)
         SELECT custcode, loccode, custshiptoname
           FROM [cimpro1].[dbo].[r2l_test] a
          WHERE NOT EXISTS
    		(SELECT * 
    		   FROM [r2lprospector].[dbo].[orgloc] b
    		  WHERE a.custcode  = b.custcode 
    		    AND a.loccode = b.loccode)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by Brett Kaiser
    Not sure I follow

    Code:
    INSERT INTO [r2lprospector].[dbo].[orgloc](custcode, loccode, custshiptoname)
         SELECT custcode, loccode, custshiptoname
           FROM [cimpro1].[dbo].[r2l_test] a
          WHERE NOT EXISTS
    		(SELECT * 
    		   FROM [r2lprospector].[dbo].[orgloc] b
    		  WHERE a.custcode  = b.custcode 
    		    AND a.loccode = b.loccode)
    What I am trying to do here is compare table one with table two. Whatever record that is in table one and not in table two needs to be inserted into table two. The thing is, my key would be 2 columns. So, custcode and loccode from first table = custcode and loccode from second table. If the combination of custcode and loccode exsist in table 1 and not in table 2, then i have to insert the whole record.

    I hope this clears it up a bit more.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmmm...

    Isn't that what I gave you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by Brett Kaiser
    ummmmm...

    Isn't that what I gave you?
    The logic seems to be right, but when I run the script, it inserts 541 records, when non should of been inserted. I think it has something to do with the custcode because there can be more than one record with the same custcode, but the record is not the same because the loccode would make it different. Records like this can exsist:

    table 1

    custcode loccode custshiptoname
    111111 00 steves bikes
    111111 01 steves computers
    111112 00 mikes beer
    111113 00 target

    So, if table 2 is missing (111111, 00, steves bikes) and I run the script, it should just insert that record and not dupilcate any otehr records that are already in the table.

    Any suggestion?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cut and paste this...


    Code:
    USE Northwind
    GO
    
    CREATE TABLE orgloc  (custcode int, loccode char(2), custshiptoname varchar(25)) 
    GO
    CREATE TABLE r2l_test(custcode int, loccode char(2), custshiptoname varchar(25)) 
    GO
    
    INSERT INTO r2l_test(custcode, loccode, custshiptoname)
    SELECT 111111, '00', 'steves bikes'	UNION ALL
    SELECT 111111, '01', 'steves computers' UNION ALL
    SELECT 111112, '00', 'mikes beer'	UNION ALL
    SELECT 111113, '00', 'target'
    GO
    
    INSERT INTO orgloc(custcode, loccode, custshiptoname)
    SELECT 111111, '01', 'steves computers' UNION ALL
    SELECT 111112, '00', 'mikes beer'	UNION ALL
    SELECT 111113, '00', 'target'
    GO
    
    SELECT * FROM orgloc
    SELECT * FROM r2l_test
    GO
    
    INSERT INTO orgloc(custcode, loccode, custshiptoname)
         SELECT custcode, loccode, custshiptoname
           FROM r2l_test a
          WHERE NOT EXISTS
    		(SELECT * 
    		   FROM orgloc b
    		  WHERE a.custcode  = b.custcode 
    		    AND a.loccode = b.loccode)
    GO
    
    SELECT * FROM orgloc
    SELECT * FROM r2l_test
    GO
    
    
    -- Cleanup
    -- DROP TABLE orgloc
    -- DROP TABLE r2l_test
    Oh and if you post questions with DDL and sample data...it's be a lot easier...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by Brett Kaiser
    Cut and paste this...


    Code:
    USE Northwind
    GO
    
    CREATE TABLE orgloc  (custcode int, loccode char(2), custshiptoname varchar(25)) 
    GO
    CREATE TABLE r2l_test(custcode int, loccode char(2), custshiptoname varchar(25)) 
    GO
    
    INSERT INTO r2l_test(custcode, loccode, custshiptoname)
    SELECT 111111, '00', 'steves bikes'	UNION ALL
    SELECT 111111, '01', 'steves computers' UNION ALL
    SELECT 111112, '00', 'mikes beer'	UNION ALL
    SELECT 111113, '00', 'target'
    GO
    
    INSERT INTO orgloc(custcode, loccode, custshiptoname)
    SELECT 111111, '01', 'steves computers' UNION ALL
    SELECT 111112, '00', 'mikes beer'	UNION ALL
    SELECT 111113, '00', 'target'
    GO
    
    SELECT * FROM orgloc
    SELECT * FROM r2l_test
    GO
    
    INSERT INTO orgloc(custcode, loccode, custshiptoname)
         SELECT custcode, loccode, custshiptoname
           FROM r2l_test a
          WHERE NOT EXISTS
    		(SELECT * 
    		   FROM orgloc b
    		  WHERE a.custcode  = b.custcode 
    		    AND a.loccode = b.loccode)
    GO
    
    SELECT * FROM orgloc
    SELECT * FROM r2l_test
    GO
    
    
    -- Cleanup
    -- DROP TABLE orgloc
    -- DROP TABLE r2l_test
    Oh and if you post questions with DDL and sample data...it's be a lot easier...

    Thank you very much. It ended up working. I think it didn't work due to this other script that I was using to update and when I ran this one, weird updates happend. But the script that you gave me did work. Thanks once again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •