Openquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Openquery

Author

Topic

patelakush
Starting Member

6 Posts

Posted - 2011-09-08 : 17:06:41

I have a following open query which works. But when I try to Insert into SQL table it does not work

USE AF_Data
declare @SQLString varchar(max)
declare @SQLOpenQuery varchar(max)
declare @Start_Date datetime
declare @End_Date datetime
SET @Start_Date = '03/01/2011'
SET @End_Date = '09/01/2011'

SET @SQLOpenQuery = 'SELECT CLCL_ID, SBSB_ID, CLCL_LOW_SVC_DT FROM FACETS.NCAV_CLCL_BASE CLCL
INNER JOIN FACETS.NCAV_SBSB_BASE SBSB ON CLCL.GRGR_CK = SBSB.GRGR_CK AND CLCL.SBSB_CK = SBSB.SBSB_CK
WHERE CLCL_INPUT_DT >= TO_DATE(''' + CONVERT(VARCHAR(30),@Start_Date,120) + ''',''YYYY-MM-DD HH24:MI:SS'')
AND CLCL_INPUT_DT <= TO_DATE(''' + CONVERT(VARCHAR(30),@End_Date,120) + ''',''YYYY-MM-DD HH24:MI:SS'')'

PRINT @SQLOpenQuery

SET @SQLString = N'INSERT CLCL_ID, SBSB_ID, CLCL_LOW_SVC_DT INTO Maketable FROM OPENQUERY(FACETSPRODRPTW, ''' + REPLACE(@SQLOpenQuery,'''','''''') + ''')'

EXEC (@SQLString)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 17:23:07

Do you mean SELECT * INTO instead?

Does Maketable already exist?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

patelakush
Starting Member

6 Posts

Posted - 2011-09-09 : 08:16:05

Make table already exit. I just want to add the open query data or Insert into make table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:31:43

the OPENQUERY syntax is wrong while inserting. it should be LIKE

[CODE]INSERT INTO Maketable
SELECT CLCL_ID, SBSB_ID, CLCL_LOW_SVC_DT FROM OPENQUERY(...[/CODE]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

patelakush
Starting Member

6 Posts

Posted - 2011-09-09 : 16:59:43

Thank you. Problem solve

quote:
Originally posted by visakh16

the OPENQUERY syntax is wrong while inserting. it should be LIKE

[CODE]INSERT INTO Maketable
SELECT CLCL_ID, SBSB_ID, CLCL_LOW_SVC_DT FROM OPENQUERY(...[/CODE]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:57:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources