Solved

sql server query

Posted on 2011-03-21
26
331 Views
Last Modified: 2012-05-11
Hi Experts

I've created a linked server in an sp using this...
SELECT TB.name, SI.rows
      FROM [10.148.22.35].reports.sys.tables AS TB
            INNER JOIN [10.146.25.35].reports.sys.sysindexes AS SI
                  ON TB.object_id = SI.id
      WHERE SI.indid < 2
       AND TB.SCHEMA_ID = 6

but after i do this i want to get that data and insert it into a sql table that i have?

Thanks
0
Comment
Question by:razza_b
  • 13
  • 5
  • 4
  • +2
26 Comments
 
LVL 1

Author Comment

by:razza_b
ID: 35182828
SELECT TB.name, SI.rows
      FROM [10.146.25.35].].reports.sys.tables AS TB
            INNER JOIN [10.146.25.35].reports.sys.sysindexes AS SI
                  ON TB.object_id = SI.id
      WHERE SI.indid < 2
       AND TB.SCHEMA_ID = 6

0
 
LVL 1

Expert Comment

by:gmartinnc
ID: 35182834
INSERT MyTable (TBName, SIRows)
SELECT TB.name, SI.rows
      FROM [10.148.22.35].reports.sys.tables AS TB
            INNER JOIN [10.146.25.35].reports.sys.sysindexes AS SI
                  ON TB.object_id = SI.id
      WHERE SI.indid < 2
       AND TB.SCHEMA_ID = 6

0
 
LVL 25

Expert Comment

by:TempDBA
ID: 35182990
though you can insert data into a table using your query, but it is not recommended performancewise.
Its better you create a stored procedure in the remote server where you do inner join between sys.tables and sys.sysindexes and then call the storedprocedure in your server.

insert mytable(tbname,SLrows)
exec [10.148.22.35].reports.dbo.<storedprocedure>

but create mytable table first.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35182991
OK fair enough,i should have elaborated more, how would i get the exact table name with certain columns from linked server when the insert into my sql table had to be supplied with this....

INSERT INTO wp4tbl_TblCompVerifySapBatch(Workorder,Docyear,movementtype,BatchNumber,Quantity,MaterialNumber)
        VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35182993
try:

INSERT INTO MyTable (TBName, SIRows)
SELECT TB.name, SI.rows
      FROM [10.148.22.35].reports.sys.tables AS TB
            INNER JOIN [10.146.25.35].reports.sys.sysindexes AS SI
                  ON TB.object_id = SI.id
      WHERE SI.indid < 2
       AND TB.SCHEMA_ID = 6
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35183027
check this query

select *
from information_schema.columns
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35183053
or join all these

select * from sys.sysindexes
select * from sys.tables
select * from sys.columns

to get the table, index and column info
0
 
LVL 1

Author Comment

by:razza_b
ID: 35183208
Sorry if im not being clear but if my SP had params of...
@Workorder,@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber

which needs to do this...
INSERT INTO wp4tbl_TblCompVerifySapBatch(Workorder,Docyear,movementtype,BatchNumber,Quantity,MaterialNumber)
        VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber)

but only if @Workorder being supplied to Linked Server to the specific sys.table can that still come back with the rest of these values so that the params can have the values matched -@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber
to help with insertion?

hope this makes sense.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35183672
Is this something thats achievable or along the lines of...

CREATE  PROCEDURE [dbo].[w4sp_getSAPData]
      @Workorder nvarchar(12)
AS
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
GO
    DECLARE  @Docyear AS VARCHAR(20)
    DECLARE  @movementtype AS VARCHAR(18)
    DECLARE  @BatchNumber AS VARCHAR(20)
    DECLARE  @Quantity AS int
    DECLARE  @MaterialNumber AS VARCHAR(18)
   
begin

        INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Quantity,MaterialNumber)
        VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber)
      
 SELECT TB.name, SI.rows
      FROM [10.148.22.35].smartreports.sys.tables AS TB
            INNER JOIN [10.148.22.35].smartreports.sys.sysindexes AS SI
                         ON TB.object_id = SI.id
      WHERE SI.indid < 2
      AND TB.SCHEMA_ID = 6      
                    
      @Docyear = TB.name.column
      @movementtype = TB.name.column
      @BatchNumber = TB.name.column
      @Quantity = TB.name.column
      @MaterialNumber = TB.name.column                                             
end
SET NOCOUNT OFF
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35183740
Could you please explain what are you trying to do? Are @DocYear,@MovementType,@BatchNumber etc, column names or the data in the columns?
0
 
LVL 1

Author Comment

by:razza_b
ID: 35183830
OK, @DocYear,@MovementType,@BatchNumber etc are column names and will have data in the colummns. e.g 25/05/11, 216, DE-90LOP

workorder param gets passed into SP.

the select query needs modified(I'll get this later when i get correct table name from SAP) so that it can use that workorder param from a certain table of linked server.

if that workorder exists i want to bring back the columns for that workorder - @DocYear,@MovementType,@BatchNumber etc

and by declaring @DocYear,@MovementType,@BatchNumber etc
it will hold the value of returned data from linked server to help with the insert query.

Hope this makes better sense.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35187727
Hi

I was looking for something along the lines of this if you think is ok..
INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
VALUES (@Workorder,TB.name.column,TB.name.column,TB.name.column,TB.name.column,TB.name.column)      
       
       SELECT TB.name.column, TB.name.column, TB.name.column, TB.name.column, TB.name.column
      FROM [10.148.22.35].smartreports.tablename AS TB
       WHERE TB.name.column = @workorder

Thanks for your replys and sorry if i wasnt so clear.
0
 
LVL 1

Expert Comment

by:gmartinnc
ID: 35190355
It sounds like you are looking for something like this:

INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
SELECT @Workorder,TB.name.column,TB.name.column,TB.name.column,TB.name.column,TB.name.column
FROM [10.148.22.35].smartreports.tablename AS TB
WHERE TB.name.column = @workorder
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 51

Expert Comment

by:HainKurt
ID: 35191094
look at this

select *
from sys.tables t
left join sys.sysindexes i on t.object_id=i.id
left join sys.columns c on t.object_id=c.object_id

then from this select what you want and use it on your insert query



INSERT INTO wp4tbl_TblCompVerifySapBatch (column names here) 
select t.name, c.name, i.rows, ...
from sys.tables t 
left join sys.sysindexes i on t.object_id=i.id 
left join sys.columns c on t.object_id=c.object_id
WHERE c.column = @workorder

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 35191440
Hi HainKurt

Im finding it hard trying to get the column names from the second select, i can see table names using the top select statement.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35191466
The table name im looking for is AUFM(which is SAP for workorder), then trying to get the columns of that table.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35191509
i used this and i can see the cloumns of that table but now i need to get the data showing in those columns for that table.

select c.name
      from [10.148.22.35].report_MRP.sys.tables t
            left join [10.146.22.35].report_MRP.sys.sysindexes i on t.object_id=i.id
            left join [10.146.22.35].report_MRP.sys.columns c on t.object_id=c.object_id
            WHERE t.name = 'AUFM'
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35191859
Are you looking for this?
DECLARE  @listStr VARCHAR(MAX) 

SELECT @listStr = COALESCE(@listStr + ',','') + c.name 
  FROM [10.148.22.35].report_MRP.sys.tables t 
       LEFT JOIN [10.146.22.35].report_MRP.sys.sysindexes i 
         ON t.object_id = i.id 
       LEFT JOIN [10.146.22.35].report_MRP.sys.columns c 
         ON t.object_id = c.object_id 
 WHERE t.name = 'AUFM' 

SELECT @listStr = 'SELECT ' + @listStr + ' FROM [10.148.22.35].report_MRP.dbo.AUFM' 

EXEC( @listStr)

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 35197153
When i run this statement i just get command completed successfully and i cant see the actual column names and values.

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35205010
Can you post the SQL query with this query? Try runnng that qurery.
DECLARE  @listStr VARCHAR(MAX) 

SELECT @listStr = COALESCE(@listStr + ',','') + c.name 
  FROM [10.148.22.35].report_MRP.sys.tables t 
       LEFT JOIN [10.146.22.35].report_MRP.sys.sysindexes i 
         ON t.object_id = i.id 
       LEFT JOIN [10.146.22.35].report_MRP.sys.columns c 
         ON t.object_id = c.object_id 
 WHERE t.name = 'AUFM' 

SELECT @listStr = 'SELECT ' + @listStr + ' FROM [10.148.22.35].report_MRP.dbo.AUFM' 

print @listStr

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 35205806
Hi

still the exact same and in the messages tab its blank.
0
 
LVL 1

Expert Comment

by:gmartinnc
ID: 35206576
Is it supposed to have different IP addresses?  I think if you adjust that, Sharath_123's solution will work for you.
0
 
LVL 1

Author Comment

by:razza_b
ID: 35206609
no i kept them all the same but still seems to show nothing.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35207588
Can you post the generated sql from the dynamic query?
0
 
LVL 1

Author Comment

by:razza_b
ID: 35207735
Sorry where from?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35212765
Is this query displaying any query?
DECLARE  @listStr VARCHAR(MAX) 

SELECT @listStr = COALESCE(@listStr + ',','') + c.name 
  FROM [10.148.22.35].report_MRP.sys.tables t 
       LEFT JOIN [10.146.22.35].report_MRP.sys.sysindexes i 
         ON t.object_id = i.id 
       LEFT JOIN [10.146.22.35].report_MRP.sys.columns c 
         ON t.object_id = c.object_id 
 WHERE t.name = 'AUFM' 

SELECT @listStr = 'SELECT ' + @listStr + ' FROM [10.148.22.35].report_MRP.dbo.AUFM' 

SELECT @listStr

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now