insert data from different tables into single table

Question

I am trying to insert data from different tables into single table
 The following one is my Query

Insert into MyAccount( [ID] , [col1] , [col2], [newColID])
select TB1.ID,TB1.col1,TB1.col2, TB2.newColID from TB1 , TB2

In this one i want to add a conditon ie if MyAccount.Id != TB1.ID

Also i need to run this Query in a specified interval of time how can i achieve this. Plz Help

Answer 1

Write the query as below:

Insert into MyAccount( [ID] , [col1] , [col2], [newColID])
select TB1.ID,TB1.col1,TB1.col2, TB2.newColID from TB1 , TB2
WHERE TB1.ID NOT IN (SELECT Id FROM MyAccount)

Regards,
Subahsh Chandra
Founder: http://SQLReality.com/blog/

Answer 2

It seems that you missing the join logic. You should have some reason/relation between these two table.

So add the required JOIN clause and add your conditions in the WHERE clause (Holds good for small volume).

For larger volume JOIN table myaccount and do the insert.

 

Sample Query for INSERT

--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;

 

Also refer this URL: http://msdn.microsoft.com/en-us/library/dd776381.aspx#OtherTables

Ananth Ramasamy Meenachi www.msarm.com

 

"Sd4u" wrote in message news:6b3918d2-0bd7-4835-a444-0f7c9d339fb0...

I am trying to insert data from different tables into single table
 The following one is my Query

Insert into MyAccount( [ID] , [col1] , [col2], [newColID])
select TB1.ID,TB1.col1,TB1.col2, TB2.newColID from TB1 , TB2

In this one i want to add a conditon ie if MyAccount.Id != TB1.ID

Also i need to run this Query in a specified interval of time how can i achieve this. Plz Help

---

--- Hope this helps. Ananth Ramasamy Meenachi http://www.msarm.com

Answer 3

You can use the below query for INSERT but mention the relationship key between TB1 and TB2 replacing "?" in the query.

You can schedule this by SQL Agent through creating a Job or can use NT task using sqlcmd utility.

INSERT INTO MyAccount( [ID] , [col1] , [col2], [newColID])
SELECT TB1.ID,TB1.col1,TB1.col2, TB2.newColID
FROM TB1
INNER JOIN TB2 ON TB1.? = TB2.?
WHERE NOT EXISTS
 (SELECT '*'
  FROM MyAccount m WHERE m.ID = TB1.ID )

Answer 4

I just want to say it's not a good idea to use NOT IN, the result set will be empty when the input of the NOT IN contains NULL. Use NOT EXISTS instead.