• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

replace into in sql

I am trying to use replace into but I get improper syntax near REPLACE
REPLACE INTO Temp_MyTools(id,ToolType,SerialNumber,FUploadTime,HUploadTime,DefaultLocationIndex,Qty) values(
  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);  

Open in new window

0
r3nder
Asked:
r3nder
  • 8
  • 4
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
r3nderAuthor Commented:
is there anyway to remove and replace what I am trying to update because I have a primary key on id
0
 
PortletPaulfreelancerCommented:
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?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain in very simple English, with a data mockup example, what exactly you're trying to pull off here.
0
 
r3nderAuthor Commented:
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  
   );  

Open in new window

0
 
PortletPaulfreelancerCommented:
"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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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'?
0
 
PortletPaulfreelancerCommented:
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?
0
 
r3nderAuthor Commented:
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);

Open in new window

0
 
r3nderAuthor Commented:
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
0
 
r3nderAuthor Commented:
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);

Open in new window

0
 
PortletPaulfreelancerCommented:
>>"The Temp_MyTools is droped ,created and filled everytime the query is run, "

then it seems you wanting "select ... INTO" which looks like this:

/* Temp_MyTools is dropped, then */

SELECT
      0 AS id
    , ToolType
    , SerialNumber
    , UploadDate AS FUploadTime
    , CAST( '20120101' AS datetime ) AS HUploadTime
    , 0 AS DefaultLocationIndex
    , 1 AS Qty
    INTO Temp_MyTools
FROM Temp_Update
;

Open in new window


BUT I'm not sure about  line 4 above. That field [ID] is auto incremented. It might work if you remove line 4 and adjust the commas

{+ edit}
don't use '2012:01:01 12:00:00AM', the safest literal to use in SQL Server is YYYYMMDD i.e. '20120101'

also, do not use single quotes for identifiers, use either brackets or double quotes. But for those aliases, neither is required
0
 
r3nderAuthor Commented:
hey portlet the error is there is already an object named 'Temp_MyTools' in the database
0
 
r3nderAuthor Commented:
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)
0
 
r3nderAuthor Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now