Solved

sql server query

Posted on 2011-03-21
26
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 56

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 56

Expert Comment

by:HainKurt
ID: 35183027
check this query

select *
from information_schema.columns
0
 
LVL 56

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 41

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
 
LVL 56

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 41

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 41

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 41

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 41

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 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