Solved

sql server query

Posted on 2011-03-21
26
336 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35183027
check this query

select *
from information_schema.columns
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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