It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2 Windows Server 2003 1GB Ram....(I know, I know)
TIA
Code Snippet GO CREATE TABLE [dbo].[Tokens]( [TokenID] [int] IDENTITY(1,1) NOT NULL, [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED ( [TokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens] ( [Token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE FUNCTION [dbo].[SplitTokenList] ( @TokenList varchar(max) ) RETURNS @ParsedList table ( Token varchar(255) ) AS BEGIN DECLARE @Token varchar(50), @Pos int SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ',' SET @Pos = CHARINDEX(',', @TokenList , 1) IF REPLACE(@TokenList , ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1))) IF @Token <> '' BEGIN INSERT INTO @ParsedList (Token) VALUES (@Token) --Use Appropriate conversion END SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos) SET @Pos = CHARINDEX(',', @TokenList, 1) END END RETURN END GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList] @TokenList varchar(max) AS BEGIN SET NOCOUNT ON; create table #Tokens (TokenID int default 0, Token varchar(50)) create clustered index Tind on #T (Token) DECLARE @NewTokens table ( TokenID int default 0, Token varchar(50) )
--Split ID's into a table INSERT INTO #Tokens(Token) SELECT Token FROM SplitTokenList(@TokenList) BEGIN TRY BEGIN TRANSACTION --get ID's for any existing tokens UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token) OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old SELECT TokenID, Token FROM #Tokens WHERE TokenID <> 0 UNION SELECT TokenID, Token FROM @Tokens COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @er nvarchar(max) SET @er = ERROR_MESSAGE(); RAISERROR(@er, 14,1); ROLLBACK TRAN END CATCH; END GO
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Ok, I want to write a stored procedure / query that says the following: Code: If any of the items in list 'A' also appear in list 'B' --return false If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF (SELECT values FROM tableA) IN(SELECT values FROM tableB) Return False ELSE Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.
The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".
Progress: The Bulk Insert task is completed. - 100 percent complete
Task Bulk Insert Task failed
Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. 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: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: } Line 1445: try { Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery(); Line 1447: return returnValue; Line 1448: }
Hi, I would like to create a Stored Procedure for obtain a list of insert using the information of a select queryI have to do X insert for days, using my start selectConstant : start dayx insert for each day if in my select I obtain 550 records and x = 100I've to do 100 insert for day with information of the selectplease note that in the final day I have to insert only 50 records and not 100 (500 and 100 are not mutiples) Thanks
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
I am trying to insert into a database Checkbox list items, I get good values up till the checkBox List and then it gives me all O's, My fields in my database are bit columns so I need the checkbox list itmes to be converted to this format. This is what i have so far. One thing is I don;t think I am inserting into the correct database columns. bitLOD, bitInjury, bitIllness, bitreferral are the database fileds.Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 'Check for the page ID to make sure not an update page then either insert new data or update existing data. Dim id As String Dim LOD As Byte Dim Injury As Byte Dim Illness As Byte Dim Referral As Byte id = Trim(Request.QueryString("ID"))For Each LItems As ListItem In CheckBoxList1.Items If LItems.Selected = True ThenSelect Case LItems.ValueCase "1" LOD = 1Case "2" Injury = 1Case "3" Illness = 1Case "4" Referral = 1 End Select End If Next 'Put data into the Database If id = "" Then 'save data into the database sql = "INSERT tblSADHealth (intTaskForceID, intUICID, strSSN, dtInjury, strNotes, LOD, Injury, Illness, Referral,) " _ & "VALUES (" & ddlTaskForce.SelectedValue & ", " & DDLUIC.SelectedValue & ", '" & txtSSN.Text & "', '" & txtStatus.Text & "', " _ & "'" & txtDate.Text & "','" & txtNotes.Text & "', " & LOD & ", " & Injury & ", " & Illness & ", " & Referral & ")" Response.Write(sql) Response.End()
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
the field "Expertise" contains available options "Accounting", "Language", "Engineering" and so on. How do I list all the available options for the field "Expertise" in ms sql 2014?
Hi All, I have never used PIVOT before but looks exactly what I want for this scenario: I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date..... I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like: DECLARE @todayDate varchar(255), DECLARE @todayPlusOne varchar(255), DECLARE @todayPlusTwo varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express. Thanks in advance. Lee
Hi, this is my second attempt to get an answer to this question. We want to strip our firstName column and lastname column of any punctuation that might be present. What's the best of doing that? Is my only choice to write a nested REPLACE for each character we want replaced (which will end up being very very long) or is there another way. thanks Zoey
