sql server query

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

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

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.

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)

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

check this query

select *
from information_schema.columns

or join all these

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

to get the table, index and column info

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.

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

Could you please explain what are you trying to do? Are @DocYear,@MovementType,@BatchNumber etc, column names or the data in the columns?

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.

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.

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

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

Select allOpen in new window

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.

The table name im looking for is AUFM(which is SAP for workorder), then trying to get the columns of that table.

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'

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)

Select allOpen in new window

When i run this statement i just get command completed successfully and i cant see the actual column names and values.

Thanks

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

Select allOpen in new window

Hi

still the exact same and in the messages tab its blank.

Is it supposed to have different IP addresses?  I think if you adjust that, Sharath_123's solution will work for you.

no i kept them all the same but still seems to show nothing.

Can you post the generated sql from the dynamic query?

Sorry where from?