replace into in sql

there is no REPLACE INTO statement.. if you are looking to insert the data, use the INSERT Statement like this

INSERT INTO Temp_MyTools(id,ToolType,SerialNumber,FUploadTime,HUploadTime,DefaultLocationIndex,Qty)
  Select 0 ,
  ToolType,
  SerialNumber,
  UploadDate , Cast('2012:01:01 12:00:00 AM'as DateTime),
  0 , 1  
  FROM Temp_Update

is there anyway to remove and replace what I am trying to update because I have a primary key on id

Replace sounds more like "update" to me (but your syntax looks like an "insert")

INSERT creates new rows
UPDATE changes existing data
MERGE allows both update and insert in a single operation (not available in all versions)

What are you trying to achieve?

Explain in very simple English, with a data mockup example, what exactly you're trying to pull off here.

I am curently witching everything from MySQL to SQL
in the temp table I created I have it set like this below - can some one give me an example of how to update and insert using my code?

 CREATE TABLE Temp_MyTools (  
  id INT IDENTITY(1,1) PRIMARY KEY,  
  ToolType varchar(2) NOT NULL unique,  
  SerialNumber INT NOT NULL unique, 
  FUploadTime  DateTime,  
  HUploadTime  DateTime,  
  DefaultLocationIndex INT,  
  Qty INT  
   );  

Select allOpen in new window

"remove and replace" isn't a typical operation either (that would be "delete" followed by "insert")

and I don't understand "because I have a primary key on id". Yes, most tables will have a primary key.

Are you trying to say you want to KEEP an existing primary key, if that is true then you definitely need "update" e.g.

update Temp_MyTools
    set HUploadTime = Cast('20120101' as DateTime)
    where id = 12345

>Explain in very simple English, with a data mockup example, what exactly you're trying to pull off here.
Translated into simpler English the above statement means 'Your question is too vague to be actionable, so try asking again.'

> witching
Like in the Wicked Witch of the West?  'The Craft'?

take a deep breath, please read my small definitions of INSERT and UPDATE

so you are moving from MySQL into MS SQL Server

this table, Temp_MyTools, exists but you want to put some data from MySQL into that table.
   if there is data already in Temp_MyTools that needs changing that is an UPDATE
   if the data is new then you need INSERT
   if you need BOTH then you can use MERGE

Does that data still sit in MySQL or have you exported from MySQL to a file of some sort?

is this right?

   MERGE Temp_MyTools MT
    USING(Select 0 as id, 
  ToolType, 
  SerialNumber,
  UploadDate as 'FUploadTime', Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'HUploadTime',
  0 as 'DefaultLocationIndex', 1 as 'Qty' FROM Temp_Update)E
  ON (MT.SerialNumber = E.SerialNumber AND MT.ToolType = E.ToolType)
  WHEN MATCHED Then
  Update SET MT.HUploadTime = E.HuploadTime ,MT.FUploadTime = E.FUploadTime
  WHEN NOT MATCHED THEN
  INSERT (MT.id,MT.ToolType,MT.SerialNumber,MT.FUploadTime,MT.HUploadTime,MT.DefaultLocationIndex,MT.Qty) 
  Values(0,E.ToolType,E.SerialNumber,E.FUploadTime,E.HUploadTime,E.DefaultLocationIndex,1);

Select allOpen in new window

The Temp_MyTools is droped ,created and filled everytime the query is run,
I am changing the data source in this application  from MySQL to SQL - so I have  alot of statements to rewrite. What I am trying to do here is some thing like replace into did.
 if there was a duplicate replace the values and if not insert it
I have exported all the data from mysql into sql
All of this is being done in sql the only thing that is Mysql are the queries I am porting over

the error I am getting is The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

   MERGE Temp_MyTools MT
    USING(Select 0 as id, 
  ToolType, 
  SerialNumber,
  UploadDate as 'FUploadTime', Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'HUploadTime',
  0 as 'DefaultLocationIndex', 1 as 'Qty' FROM Temp_Update)E
  ON (MT.SerialNumber = E.SerialNumber AND MT.ToolType = E.ToolType)
  WHEN MATCHED Then
  Update SET MT.HUploadTime = E.HuploadTime ,MT.FUploadTime = E.FUploadTime
  WHEN NOT MATCHED THEN
  INSERT (MT.id,MT.ToolType,MT.SerialNumber,MT.FUploadTime,MT.HUploadTime,MT.DefaultLocationIndex,MT.Qty) 
  Values(0,E.ToolType,E.SerialNumber,E.FUploadTime,E.HUploadTime,E.DefaultLocationIndex,1);

Select allOpen in new window

hey portlet the error is there is already an object named 'Temp_MyTools' in the database

I did an insert into select from and then a distinct on the final query and you were right the unique id's and the auto increment got in the way - don't know why I did it in the first place but suffice it to say it is done and verified as accurate (data wise)

Thank you very much