Insert and update

[Sasquatch69]

Hi,

I need to update a table1 from table1 in a SQL SERVER database. I can use update without problem, but i also need to insert new value from table 2 in table1.

I know i can use "replace into" in MYSQL(replace and insert).
but it seem that it not working in SQL server. i really need to do a INSERT and UPDATE or......??

thanks
ps: Sorry for my bad english.

 

[wsmall73]

INSERT INTO table1 (col1,col2) SELECT col1,col2 FROM Table2

hopefully this is what you were looking for.

 

[Sasquatch69]

thanks Wsmall73,

But i got a little problem because i have a primary key.
i want to update(or replace) the record but i can't with insert into.

 

[wsmall73]

can you add to your where statement to say where col1 not in (SELECT col1 FROM Table1)

INSERT INTO table1 (col1,col2) SELECT col1,col2 FROM Table2 WHERE col1 NOT IN(SELECT col1 FROM Table1)

where col1 is your primary key.. I am not even sure if that will work for you or not....

 

[tlbroadbent]

--Update existing rows.
UPDATE Table1 Set
Col2=table2.col2,
Col3=tabl;e2.col3, ...
ColN=table2.colN
FROM table1 INNER JOIN table2
ON tabl1.PK=table2.KeyCol

--Insert new rows.
INSERT INTO table1 (PK, col2, col3, ..., colN)
SELECT KeyCol, col2, col3, ...,colN
FROM Table2
WHERE NOT EXISTS
(SELECT * FROM Table1
WHERE PK=table2.KeyCol) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Sasquatch69]

thank you guys,

I will use a insert and update(Terry solution). It work very well. thanks a lot