Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

sql server query

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
razza_b
Asked:
razza_b
  • 13
  • 5
  • 4
  • +2
1 Solution
 
razza_bAuthor Commented:
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
 
gmartinncCommented:
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
 
TempDBACommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
razza_bAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
check this query

select *
from information_schema.columns
0
 
HainKurtSr. System AnalystCommented:
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
 
razza_bAuthor Commented:
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
 
razza_bAuthor Commented:
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
 
SharathData EngineerCommented:
Could you please explain what are you trying to do? Are @DocYear,@MovementType,@BatchNumber etc, column names or the data in the columns?
0
 
razza_bAuthor Commented:
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
 
razza_bAuthor Commented:
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
 
gmartinncCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
razza_bAuthor Commented:
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
 
razza_bAuthor Commented:
The table name im looking for is AUFM(which is SAP for workorder), then trying to get the columns of that table.
0
 
razza_bAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
razza_bAuthor Commented:
When i run this statement i just get command completed successfully and i cant see the actual column names and values.

Thanks
0
 
SharathData EngineerCommented:
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
 
razza_bAuthor Commented:
Hi

still the exact same and in the messages tab its blank.
0
 
gmartinncCommented:
Is it supposed to have different IP addresses?  I think if you adjust that, Sharath_123's solution will work for you.
0
 
razza_bAuthor Commented:
no i kept them all the same but still seems to show nothing.
0
 
SharathData EngineerCommented:
Can you post the generated sql from the dynamic query?
0
 
razza_bAuthor Commented:
Sorry where from?
0
 
SharathData EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now