Start Free Trial

asked on

SQL QUERY to Table question

I have a c# asp.net application. Related to this application I have a complex query that gets data from a remote database, call it query-remote. I need to place all of the query-remote results from the remote database into an exiting table in a local database (.mdf) using code in the c# asp.net application . This local database table has matching columns with the query-remote result set. Can someone please provide an example for this sprint.

Try to have a look:
https://msdn.microsoft.com/en-us/library/ms140052.aspx

ASKER

Thanks but what does this have to do with my question?

@Author - could you please post you query here, Shall modify it to make it work for you.

ASKER

Thanks for the reply, We have a few but these 2 are the ones we need to send to table as described in the question

query #1

SELECT
Q.TicketNumber AS [WO Number]
,Q. [OEM WO Number]    
,Q. [Failure Site]    
,Q.PartNumber
,Q.Description
,Q.SerialNumber
,Q.Status
,Q.ConditionCode
,Q.CompanyName
,Q.TroubleType
,Q.DateTimeCreated
,Q.[Date Received at OEM]
,Q.[DEPOT Ship Time(Hours)]
,Q.[OEM DEPOT Induction Date]
,Q.[Date Completed OEM Repair]
,Q.[DMDT(HoursClosed)]
--,(CASE
--   WHEN Q.[DMDT(HoursClosed)] is Null or Q.[DMDT(HoursClosed)] ='0'
--
--    THEN (DateDiff(Hour,Q.[OEM DEPOT Induction Date],Q.[Date Closed]))
--    ELSE Q.[DMDT(HoursClosed)]
--END)
,Q.[FRACAS Induction Date]
,Q.[Total Down 1 Time(Hours)]
,Q.[Date Closed]
,Q.[Total Down 2 Time(Hours)]
,Q.TimeDateReported
,Q.TimeDateOccurred
,Q.Owner

FROM (

SELECT        
TroubleTicket.TicketNumber
,TroubleTicket.UserText01 as [OEM WO Number]    
,Site.Name AS [Failure Site]    
,dbo.vt_Asset.PartNumber    
,dbo.vt_Asset.Description  
,dbo.vt_Asset.SerialNumber  
,dbo.vt_Asset.Status   
,dbo.vt_Asset.ConditionCode  
,dbo.vt_Asset.CompanyName    
,TroubleTicket.TroubleType      
,TroubleTicket.DateTimeCreated    
,TroubleTicket.UserDate01 AS [Date Received at OEM]    
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.UserDate01) as [DEPOT Ship Time(Hours)]    
,TroubleTicket.UserDate02 AS [OEM DEPOT Induction Date]    
,TroubleTicket.UserDate04 AS [Date Completed OEM Repair]     
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate04) as [DMDT(HoursClosed)]
,TroubleTicket.UserDate03 AS [FRACAS Induction Date]     
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate03) as [Total Down 1 Time(Hours)]    
,TroubleTicket.DateClosed AS [Date Closed]  
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.DateClosed) as [Total Down 2 Time(Hours)]    
,TroubleTicket.TimeDateReported       
,TroubleTicket.TimeDateOccurred       
,dbo.vt_Asset.Owner    
       
FROM TroubleTicket       
       
LEFT JOIN dbo.vt_Asset       
ON TroubleTicket.AssetId = dbo.vt_Asset.Id       
    
LEFT JOIN Site    
ON TroubleTicket.SiteIdFailure = Site.Id    
       
WHERE       
(TroubleTicket.TroubleType = 'UM' OR TroubleTicket.TroubleType = 'SM')       
AND TroubleTicket.UserDate01 is not null       
AND DateClosed is not null       
AND dbo.vt_Asset.Owner = 'GrayEagle'       
--AND TroubleTicket.DateClosed between {@ClosedStartDate,D} and {@ClosedEndDate,D}       
AND TroubleTicket.DateClosed between '04/01/2016' AND '07/01/2016' 
) as Q

WHERE
Q.ConditionCode = 'A'
AND Q. [OEM WO Number] > '0'

ORDER BY       
Q.[Date Completed OEM Repair] DESC

Open in new window

===========================================================
QUERY#2

SELECT  
Q.TicketNumber AS [WO Number]
,Q. [OEM WO Number]    
,Q. [Failure Site]    
,Q.PartNumber
,Q.Description
,Q.SerialNumber
,Q.Status
,Q.ConditionCode
,Q.CompanyName
,Q.TroubleType
,Q.DateTimeCreated
,Q.[Date Received at OEM]
,Q.[DEPOT Ship Time(Hours)]
,Q.[OEM DEPOT Induction Date]
,Q.[Date Completed OEM Repair]
,Q.[DMDT_Hours]

  
,DATEDIFF(hour,'6/30/2016',Q.[OEM DEPOT Induction Date]) AS [DMDT(Calc)]
,Q.[FRACAS Induction Date]
,DATEDIFF(hour,Q.[FRACAS Induction Date], Q. [OEM DEPOT Induction Date]) AS [FRACAS Add]
,Q.[Total Down Time(Hours)]
,Q.DateClosed
,Q.[Down Time(Hours)]
,Q.TimeDateReported
,Q.TimeDateOccurred
,Q.Owner

FROM (

SELECT        
TroubleTicket.TicketNumber
,TroubleTicket.UserText01 as [OEM WO Number]    
,Site.Name AS [Failure Site]    
,dbo.vt_Asset.PartNumber    
,dbo.vt_Asset.Description  
,dbo.vt_Asset.SerialNumber  
,dbo.vt_Asset.Status   
,dbo.vt_Asset.ConditionCode  
,dbo.vt_Asset.CompanyName    
,TroubleTicket.TroubleType      
,TroubleTicket.DateTimeCreated    
,TroubleTicket.UserDate01 AS [Date Received at OEM]    
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.UserDate01) as [DEPOT Ship Time(Hours)]    
,CONVERT(VARCHAR(10), TroubleTicket.UserDate02, 101) AS [OEM DEPOT Induction Date]
,CONVERT(VARCHAR(10),TroubleTicket.UserDate04,101) AS [Date Completed OEM Repair]
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate04) as DMDT_Hours

,CONVERT(VARCHAR(10),TroubleTicket.UserDate03,101) AS [FRACAS Induction Date]
,Convert(Int,(DATEDIFF(Hour,TroubleTicket.UserDate03,TroubleTicket.UserDate02))) AS [FRACAS Added]
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate03) as [Total Down Time(Hours)]    
,TroubleTicket.DateClosed      
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.DateClosed) as [Down Time(Hours)]    
,TroubleTicket.TimeDateReported       
,TroubleTicket.TimeDateOccurred       
,dbo.vt_Asset.Owner    
      
FROM TroubleTicket       
     


LEFT JOIN dbo.vt_Asset       
ON TroubleTicket.AssetId = dbo.vt_Asset.Id       
    
LEFT JOIN Site    
ON TroubleTicket.SiteIdFailure = Site.Id    
       
WHERE       
(TroubleTicket.TroubleType = 'UM' OR TroubleTicket.TroubleType = 'SM')       
AND TroubleTicket.UserDate01 is not null       
AND DateClosed is null       
AND dbo.vt_Asset.Owner = 'GrayEagle'           
       

) as Q

WHERE
Q.[OEM WO Number] > '0'
AND Q.[OEM DEPOT Induction Date] < '07/01/2016'  --DATE NEEDS TO BE PROVIDED AS THE LAST DAY OF THE SCORING PERIOD
AND
(
(Q.[Date Completed OEM Repair] is null) OR (Q.[Date Completed OEM Repair] = ' ')
     OR 
(Q.ConditionCode = 'A' AND Q.[Date Completed OEM Repair] is not null)
)
AND(
((
(Q.[FRACAS Induction Date] is not Null) AND (Q.[FRACAS Added] NOT BETWEEN '-7' and '7')
     OR
(Q.[FRACAS Induction Date] is Null)
)
AND
(
(Q.[Date Completed OEM Repair] is Null) AND (Q.[FRACAS Added] NOT BETWEEN '-7' and '7')
    OR
(Q.[Date Completed OEM Repair] is not Null)
))
)

ORDER BY       
Q.[Date Completed OEM Repair]

Open in new window

What you can do here is below..

--

INSERT INTO [ServerName].[databasename].[dbo].[tableName]
Your select query...


--

Open in new window

ASKER

please expand, i dont quite understand

Replace your query with below and you will data in your table

NOte that you have to replace [ServerName].[databasename].[dbo].[tableName] from actual servername, dbname and tablename.

--


INSERT INTO [ServerName].[databasename].[dbo].[tableName]
SELECT  
Q.TicketNumber AS [WO Number]
,Q. [OEM WO Number]    
,Q. [Failure Site]    
,Q.PartNumber
,Q.Description
,Q.SerialNumber
,Q.Status
,Q.ConditionCode
,Q.CompanyName
,Q.TroubleType
,Q.DateTimeCreated
,Q.[Date Received at OEM]
,Q.[DEPOT Ship Time(Hours)]
,Q.[OEM DEPOT Induction Date]
,Q.[Date Completed OEM Repair]
,Q.[DMDT_Hours]

  
,DATEDIFF(hour,'6/30/2016',Q.[OEM DEPOT Induction Date]) AS [DMDT(Calc)]
,Q.[FRACAS Induction Date]
,DATEDIFF(hour,Q.[FRACAS Induction Date], Q. [OEM DEPOT Induction Date]) AS [FRACAS Add]
,Q.[Total Down Time(Hours)]
,Q.DateClosed
,Q.[Down Time(Hours)]
,Q.TimeDateReported
,Q.TimeDateOccurred
,Q.Owner

FROM (

SELECT        
TroubleTicket.TicketNumber
,TroubleTicket.UserText01 as [OEM WO Number]    
,Site.Name AS [Failure Site]    
,dbo.vt_Asset.PartNumber    
,dbo.vt_Asset.Description  
,dbo.vt_Asset.SerialNumber  
,dbo.vt_Asset.Status   
,dbo.vt_Asset.ConditionCode  
,dbo.vt_Asset.CompanyName    
,TroubleTicket.TroubleType      
,TroubleTicket.DateTimeCreated    
,TroubleTicket.UserDate01 AS [Date Received at OEM]    
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.UserDate01) as [DEPOT Ship Time(Hours)]    
,CONVERT(VARCHAR(10), TroubleTicket.UserDate02, 101) AS [OEM DEPOT Induction Date]
,CONVERT(VARCHAR(10),TroubleTicket.UserDate04,101) AS [Date Completed OEM Repair]
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate04) as DMDT_Hours

,CONVERT(VARCHAR(10),TroubleTicket.UserDate03,101) AS [FRACAS Induction Date]
,Convert(Int,(DATEDIFF(Hour,TroubleTicket.UserDate03,TroubleTicket.UserDate02))) AS [FRACAS Added]
,DateDiff (hour,TroubleTicket.UserDate02,TroubleTicket.UserDate03) as [Total Down Time(Hours)]    
,TroubleTicket.DateClosed      
,DateDiff (hour,TroubleTicket.DateTimeCreated,TroubleTicket.DateClosed) as [Down Time(Hours)]    
,TroubleTicket.TimeDateReported       
,TroubleTicket.TimeDateOccurred       
,dbo.vt_Asset.Owner    
      
FROM TroubleTicket       
     


LEFT JOIN dbo.vt_Asset       
ON TroubleTicket.AssetId = dbo.vt_Asset.Id       
    
LEFT JOIN Site    
ON TroubleTicket.SiteIdFailure = Site.Id    
       
WHERE       
(TroubleTicket.TroubleType = 'UM' OR TroubleTicket.TroubleType = 'SM')       
AND TroubleTicket.UserDate01 is not null       
AND DateClosed is null       
AND dbo.vt_Asset.Owner = 'GrayEagle'           
       

) as Q

WHERE
Q.[OEM WO Number] > '0'
AND Q.[OEM DEPOT Induction Date] < '07/01/2016'  --DATE NEEDS TO BE PROVIDED AS THE LAST DAY OF THE SCORING PERIOD
AND
(
(Q.[Date Completed OEM Repair] is null) OR (Q.[Date Completed OEM Repair] = ' ')
     OR 
(Q.ConditionCode = 'A' AND Q.[Date Completed OEM Repair] is not null)
)
AND(
((
(Q.[FRACAS Induction Date] is not Null) AND (Q.[FRACAS Added] NOT BETWEEN '-7' and '7')
     OR
(Q.[FRACAS Induction Date] is Null)
)
AND
(
(Q.[Date Completed OEM Repair] is Null) AND (Q.[FRACAS Added] NOT BETWEEN '-7' and '7')
    OR
(Q.[Date Completed OEM Repair] is not Null)
))
)

ORDER BY       
Q.[Date Completed OEM Repair]

--

Open in new window

@Author - Have you tried the above approach ?

ASKER

the query is called on a remote server, I have that covered, the table I need to write the query to is local, do i still need servername and if so is that the machine name [servername] where the appllication is located

Four part name [ServerName].[databasename].[dbo].[tableName] will work from anywhere. Pls try.

ASKER

i get error saying to many prefixes, maximum number is 3

could you please post the complete error ?

ASKER

I think the problem is with servername. The application and the database with the target table will reside on the same system so is the servername the computername, full computer name, or some other name? this is all being done in c# so databases are opened and command is issued to execute the queries. for example the database with the target table has a connection string of (localdb)\mssqllocaldb

If that's the case don't pass server name. !!

ASKER

I get "invalid object name" error using ...
[ServerName].[databasename].[dbo].[tableName]

or
[databasename].[dbo].[tableName]

could you please post complete query ?

ASKER

INSERT INTO [TNW-L-RM2].[DMDTDataSource.mdf].[dbo].[OpenWoHours]
-- local servername dbasename target table

---this is the start of the query that will provide the data to be inserted in to the table listed above.
SELECT
Q.TicketNumber AS [WO Number]
,Q. [OEM WO Number]
,Q. [Failure Site]
,Q.PartNumber
,Q.Description

database name is incorrect , we cannot have . (dot) in the database name. Pls provide proper DB name, what you are providing in the name of the mdf file.

ASKER

i tried the database file without extension as well but got the same error. I will try again, did everything else look to be formatted correctly....
I also wanted to say thank you for your fast response and willingness to help.

ASKER

still have errors, they are
1 Could not find server 'TNW-L-RM2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Do you have linked server created on your server for the remote server TNW-L-RM2? You can check that by querying sys.servers.

ASKER

Yes we linked the serrvers and then started getting NT AUTHORITY\anonymous logon

TNW-L-RM2= LOCAL
THE OTHER SERVER IS REMOTE

We could not get this to work so we are looking at doing a query sqldatareader and populating the table from there in the c# code behind, do you have any experience with this method

I think it might be a double hop error. you may try setting up Kerberos Authentication.

Here is an article on that.
http://www.sqlservercentral.com/articles/Security/65169/

ASKER

a little too much trouble for dev, we will be on a single server at deployment anyway, we will need to move on the code as the application will use, we were trying to dev the app accross a network where the dbases were on separate machines. If you know anything about the alternative method I mentioned please advise otherwise I will close this and post another question, thank you for your help either way.

Have you created linked server, if not try using below...

--

EXEC sp_addlinkedserver   
   @server='yourServer', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='yourServer\instance1';

--

Open in new window

>> We could not get this to work so we are looking at doing a query sqldatareader and populating the table from there in the c# code behind

Are you looking for a solution with sqldatareader in C# or want to fix the linked server issue?

ASKER

looking for a solution with sqldatareader in C#

Haven't really worked with that. msdn links may help you to get started with that.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx
You can wait for a C# expert to comment on this.

ASKER

THANK VERY MUCH

Gautham Janardhan

you can read using a reader like this

// declare the SqlDataReader, which is used in
			// both the try block and the finally block
			SqlDataReader rdr = null;

			// create a connection object
			SqlConnection conn = new SqlConnection(pick ur connection string from webconfig);

			// create a command object
			SqlCommand cmd  = new SqlCommand(your complex query here, conn);

			try
			{
				// open the connection
				conn.Open();

				// 1. get an instance of the SqlDataReader
				rdr = cmd.ExecuteReader();

				while (rdr.Read())
				{
					// get the results of each column
					string col1= (string)rdr["column names here"];

// insert into you MDF here


				}
			}
			finally
			{
				// 3. close the reader
				if (rdr != null)
				{
					rdr.Close();
				}

				// close the connection
				if (conn != null)
				{
					conn.Close();
				}
			}

Open in new window

ASKER CERTIFIED SOLUTION

Gautham Janardhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

thank you, exactly what needed