.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

insert query with subquery

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hello, I have 2 tables, one fact table (FeitDienstDagen) which contains employments, and a reference/dimension table, vDimMedewerker,  which contains a history of employees and their departments. IN the fact table I want to insert the correct WerkID from vDimMedewerker. So the employeeID (ms120_obj) must be same,  and the DienstDag_KEY (int date Key) must be greater then or equal to  the first werk_start_KEY (int date key) for that employee,  Ordered descendant. How to do that? Her my query, but it doesn't do the job :-) USE TEAMSOFT_TBSDB GO DELETE FROM dbo.FeitDienstdagen GO INSERT INTO [TEAMSOFT_TBSDB].[dbo].[FeitDienstdagen]            ([DienstverbandID]            ,[Deeltijdfactor]            ,[DienstDag_KEY]            ,[MutatieID]            ,[DienstverbandStart]            ,[DienstverbandEind]            ,[UrenWeek]            ,[Contracturen]            ,[UrenDag]     

View Complete Post

More Related Resource Links

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

How to write query with subquery more efficient

Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

insert query from ms SQL server into a mysql linked server

INSERT into openquery(dbserver1MySQL,'select * from graham.lookup_in_table') select * from NavteqAPAC.dbo.Admin_Names   is giving me the following error! Please help me   OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can't run this command now". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" could not INSERT INTO table "[MSDASQL]".

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

record count for subquery which is used for INSERT

My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

How to write SQL Insert into query using LINQ


How I can write following SQL query using LINQ. There is no default constraint define for the ID column so we have explicitly pass the NEWID().

I want to replicate the products of a company to another company.

Insert Into Products
Select NEWID() AS ProductID, ProductName, CompanyID = @DestinationID, ProductCategoryID From Products Where CompanyID = @SourceID

Also once I get these result how can I use InsertAllOnSubmit to insert all the records at once without looping it.

If not Exist Insert Query


Hi I have the syntax for the following query but dont understand why  UsePrimayKeyHere = 'UsePrimaryKeyHere' is in Where clause?


(SELECT 1 FROM [EligStaging].[Uaqa].[CrosswalkTableNameHere]

 WHERE [UsePrimaryKeyHere] = UsePrimaryKeyHere')


Insert Column Limit from XML? The query processor ran out of stack space during query optimization e


I get the following error:

Msg 8621, Level 17, State 2, Procedure ProcessFliteTracWorkingXML_FTPax, Line 348
The query processor ran out of stack space during query optimization. Please simplify the query.

I have looked at this KB fix, but we are a newer version then in that article:

Also found this post with a similiar issue, but no solution:

Before I call Microsoft Support I thought I would try here first.

I am trying to run a stored proc that basically needs to parse xml messages coming from an outside datasource.  We are using SQL Server 2005 SP3 (9.00.4053.00 SP3 Standard Edition).  This datasource is an old pervasived database with not the best data model.  So what we wanted to do was pull the raw data as is into a staging table and then convert it to a better data model in our ODS.  There are around 300 columns in this table and I have a generic process that pulls the rows out and converts to XML and saves to a working table (this

Insert Query - Help Please!


Hello, I am attempting to do an insert query on a webpage for a simple member registration form I have created in vs2010. I have done this type of thing before without problem using an access database, but now I'm using a sqlexpress db created from within vs2010. This is the screenshot of my insert query:

Insert Query

Now, when I execute this query in the form, I get an error that says:

Must declare the scalar variable "@FirstName".

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@FirstName".

Source Error: 

Error on Insert query


I'm getting an error on the following query that I don't quite understand:


[DB names changed to keep the associated apps at least somewhat anonymous :)   ]

insert into DB1.dbo.CUST

select 'T', cast(CustomerID as varchar(15)),
DB2.dbo.getcustomerattributevalue(CustomerId, 'FirstName') + ' ' + DB2.dbo.getcustomerattributevalue(CustomerId, 'LastName'),
 'DL', 'DCH',
RegistrationDate, 'USD' 
from DB2.dbo.Customer where CustomerID = 42


The error that I'm getting is the following:

Msg 8114, Level 16, State 5, Line 1

Error converting data type varc

Need Query to find out the latest record insert in database


Here is my table contain the following fields

   ID    UID    OID   Date  

   1      20       1    2010-11-03 12:22:44.217

   2      30       1     2010-11-03 12:01:09.090  

   3      20      2     2010-11-03 12:22:44.217

   4      40      2   2010-11-03 12:01:09.090

i need the result like

UID   OID    Date

 30     1        2010-11-03 12:01:09.090  

 40     2         2010-11-03 12:01:09.090  

Please help me

20 2 2010-11-03 12:22:44.217

T-SQL query to insert in Temp Table

I need an expert suggestion on a SQL problem.The scenario is as follows

" I have a temporary table which has 10 rows and it is ordered by Name.
  I need to insert a row which should get inserted as the 1st row and the order by should not apply to the same "

How to insert variables inside a query?




i've facing this problem in my program..


Dim a As Integer = 1
Do Until a = 12
            Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim cmd As System.Data.SqlClient.SqlCommand = New SqlCommand

                cmd.CommandText = "update it_report set network=(select count(*) from tbl_form_it_service where usercase in ('NETWK') and datepart(year,reportdatetime)= '2010'  and datepart(month,reportdatetime) = " & a & ") where year=2010 and month= " & month & "" 
                cmd.Connection = con


                DataGrid1.DataSource = cmd.ExecuteReader()


            Catch ex As Exception
                Message.Text = "Error! " & ex.ToString
                Exit Sub
            End Try

            a = a + 1


how can i insert  loop statement variable into the query?? and how to insert textfield id in the query also? 
2010 will be textfield value.

help me,

Remote insert speeds query time from 5.5 mins to 3 seconds?



I have a query running in SQL Server 2005 sp2 that takes just over 5½ minutes to run despite my best efforts to speed it up.

It is an INSERT INTO local table SELECT FROM sql statement.  If I just run the SELECT part, it still takes over 5½ minutes.  The query is currently returning 2 rows of data.

However, If I change the table it inserts into to be an identical table on a remote server using a linked server, then it runs in 3 seconds????

I like the time it now takes, but I do not understand why it is so much faster inserting into a table on a remote server.  If anything, I expected it to take a little longer - anyone have any ideas?

These are examples of the query:
--Takes approximately 05:26 to complete
SELECT Field1, Field2, Field3

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Insert Rows with a GridView

In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend