Msg 156, level 15, state 1, line 23 incorrect syntax near the keyword 'from'.

Question

0.00/5 (No votes)

See more:

I have is query. That I'm getting an error message "Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'FROM'." It is pointing to the query "FROM [TOMSQLVISION].[VISION_2020].[REAL_PROP].[VW_HSALE]." This query pulls in data from 1 sql server and creates a table in another sql server. Then populates a web page.

SQL

  1  DROP TABLE [dbo].[VISION_SALEHIST4]
  2  SELECT 
  3  
  4    MNC,
  5    PID,
  6    LINE_NUM,
  7    BOOK_PG,
  8    CONVERT(datetime, SALEDATE) AS SALEDATE,
  9    SALEPRICE,
 10    CERTIFICATE,
 11    INSTRUMENT,
 12    QUALIFIED,
 13    OWN_NAME,
 14    CO_OWN_NAME,
 15    ADDRESS1,
 16    ADDRESS2,
 17    CITY,
 18    STATE,
 19    ZIP
 20    
 21  INSERT INTO [dbo].[VISION_SALEHIST4]
 22   
 23    FROM [TOMSQLVISION].[VISION_2020].[REAL_PROP].[VW_HSALE]

What I have tried:

I'm connected to the other server tomsqlvision. The table that query is pullilng the data from is a view table. Would that make a difference?

Thanks for the help.

Posted 10-Aug-20 8:53am

Chris Slinko

Updated 10-Aug-20 10:08am

User 11060979

v2

Add a Solution

Comments

[no name] 10-Aug-20 14:59pm

Think about INSERT INTO <table> (<columns>) SELECT <columns> ... FROM ...

Read this: SQL INSERT INTO Statement[^]

[no name] 10-Aug-20 15:14pm

Read this: SQL INSERT INTO Statement[^]

[no name] 10-Aug-20 15:16pm

Sorry some inter...whatever... what I mean is something like

INSERT INTO [dbo].[VISION_SALEHIST4] (<columns>) SELECT <columns> FROM [TOMSQLVISION].[VISION_2020].[REAL_PROP].[VW_HSALE]

[Edit]
And DROP TABLE [dbo].[VISION_SALEHIST4]is a bad idea without to create it again before try to insert.

Either you create the table again or instead of drop table you do this
DELETE FROM [dbo].[VISION_SALEHIST4]

Jassom 10-Aug-20 15:25pm

You dropped the table at first .. and then how would you insert values into a dropped table???

[no name] 10-Aug-20 15:27pm

Yep ;)

Jassom 10-Aug-20 15:52pm

DELETE FROM [dbo].[VISION_SALEHIST4]
GO

INSERT INTO [dbo].[VISION_SALEHIST4] SELECT * FROM [TOMSQLVISION].[VISION_2020].[REAL_PROP].[VW_HSALE]
GO

Note: It must be both tables have the same columns and the same order.

[no name] 10-Aug-20 16:07pm

Quote: "Note: It must be both tables have the same columns and the same order."
Yes and no. As long you specify each column for INSERT and (in the same order) for SELECT at least the column names can be different.
E.g.


INSERT INTO TBl1(col1, col2) SELECT(ColA, ColB) FROM TBl2

will work.

... and of course the types of the columns need to match.

Jassom 10-Aug-20 16:26pm

Yes, in case you will not use the (*), You can choose specific columns, as you said.

[no name] 10-Aug-20 16:29pm

... which is mandatory in case column names do not match ;)

Jassom 10-Aug-20 16:47pm

it doesn't matter if the column names doesn't match.
In case you choose to use (*), only what should match are these below:
1- type of columns.
2- number of columns.

but column names doesn't matter

[no name] 10-Aug-20 16:49pm

no

Jassom 10-Aug-20 17:32pm

I tried it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jassom · Answer 1 · 2020-08-10T10:08:00

Solution 1

You may find what you want in the link below:

sql - INSERT INTO from two different server database - Stack Overflow[^]

try it.

Posted 10-Aug-20 10:08am

Jassom