SQL Insert into table

[DavidSkilbeck]

Hi,
can I use 

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Or is there some other method?

Thanks,

[tfranklin · Accepted solution]

you absolutely can but you need to ensure that the columns match up or explicitly define them in the select statement.  I've also noticed that the application doesn't like it if you use parenthases to wrap the select statement.

[DavidSkilbeck]

@tfranklin 

So something like below should work in a mimic script

Set Table1 = Server.FindObject("XXX.XXX1")

Set Table2 = Server.FindObject("XXX.XXX2")

SelectedRow = Mimic.Layers("XXX").Item("Lst_1").Current1stColDataStr

Set Test = Server.Query("INSERT INTO XXX2 (column1, column2, column3) SELECT column1, column2, column3 FROM XXX1 WHERE ID = '" + SelectedRow + "'" + "")

Thanks,

[tfranklin]

yeah, looks like it would work.  i just ran this on a test datatable and it worked as well.  it's more or less the same as what you have.

insert into TestDT (FN,ID,Source,CurrentTime) (SELECT FULLNAME,ID,SOURCE,CURRENTTIME FROM CDBPOINT)

[DavidSkilbeck]

@tfranklin

Hi, it hasn't worked for me.
I am guessing it doesn't have to be CDBPoint does it?

One of the tables is in a different file path / folder structure, does this matter?

Set Note = Server.Query("INSERT INTO WONSP (ID, ET, Mes, User, Priority, Desc, Status, EID, Cat, NoteNo, SID) (SELECT ID, ET, Mes, User, Priority, Desc, Status, EID, Cat, NoteNo, SID FROM WONS")

Must be getting the syntax wrong.

Thanks,

[tfranklin]

Folder path shouldn't matter at all.  Looks like you're missing a ) in the query syntax.  Try the below

Set Note = Server.Query("INSERT INTO WONSP (ID, ET, Mes, User, Priority, Desc, Status, EID, Cat, NoteNo, SID) (SELECT ID, ET, Mes, User, Priority, Desc, Status, EID, Cat, NoteNo, SID FROM WONS)")

[DavidSkilbeck]

@tfranklin 

Hi, the owner of the source table had not only renamed the table, but also changed the format.

I had asked. Arrrrr.

I had tried a number of syntax combinations previously, which obviously would never have worked.

tfranklin thanks for your help and persistence with it.

It was a big help! I didn't know if what I was trying to do could be done.

I will mark your post as the answer.

Thanks,