Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .
View 8 RepliesI have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
Hi,
How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.
thanks.
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.
-- Inserting new preferred supplier into R5CATALOGUE
DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)
[code]....
How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is
DECLARE @d DateTIme = GETDATE()
DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
SET @c = @c + 1
END
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
Hi everyone,I tried to upload my database to my web host. Nevertheless as they donot give me enough permission to perform some DTS thus I have togenerate script to do it myself. The problem is MSSQL scriptgeneration operation can only generate the definitions of the objects,no data insertion sql is created.So is there any method to create insertion script for all the tablesin my database (they have automated increment key as well)Hope for your response!Thank you
View 2 Replies View RelatedI have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
ok I have a stored procedure......
I pass in the variables that are requried....What is the best way to add a record
using my stored procedure in VB.net code in a button click event......
How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn
CREATE procedure dbo.Appt_AddAppt
(
@ClinicID int,
@AccountNum nvarchar(10),
@DOS nvarchar(12),
@POSCODE nvarchar(5)
)
as
Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID)
Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)
GO
I have an SP to add a record to the database but i get the error shown below. Any help appreciated.
Stored Procedure:
CREATE PROCEDURE addUser
@username char(15),
@password char(12)
AS
INSERT INTO users(username, password) VALUES (@username, @password)
GO
Code calling SP:
Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
Dim cmd As New SqlCommand("addUser", myConnection)
MyDataAdapter = New SqlDataAdapter()
'MyDataAdapter = New SqlDataAdapter("addUser", myConnection)
With cmd 'MyDataAdapter
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@username", SqlDbType.Char).Value = username
.Parameters.Add("@password", SqlDbType.Char).Value = password
End With
Try
myConnection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Finally
myConnection.Close()
End Try
I get an error message on the line: cmd.ExecuteNonQuery()
System.FormatException: Input string was not in a correct format.
Hello. I currently have a website that allows appointments to be booked up for doctors. i currently have an insert record page built using ASP components. I would like to introduce some validation so that if a user tries to book an appointment date and time that is already occupied, an error message is returned saying that appointment slot is already booked. I'm quite confused how to do this. My current code looks as follows.
<%@ Page Language="C#" MasterPageFile="~/AdministratorMasterPage.master" AutoEventWireup="true" CodeFile="AddAppointment.aspx.cs" Inherits="AddAppointment" Title="Add Appointments - Bournemouth and Poole NHS Primary Care Trust" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table style="position: relative">
<tr>
<td style="width: 45px">
<br />
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:SqlDataSource ID="AppointmentsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
DeleteCommand="DELETE FROM [Appointment] WHERE [PatientNo] = @PatientNo" InsertCommand="INSERT INTO [Appointment] ([PatientNo], [PatientSurname], [PatientForename], [ConsultantName], [HospitalName], [Time], [Date], [AppointmentStatus]) VALUES (@PatientNo, @PatientSurname, @PatientForename, @ConsultantName, @HospitalName, @Time, @Date, @AppointmentStatus)"
SelectCommand="SELECT * FROM [Appointment]" UpdateCommand="UPDATE [Appointment] SET [ConsultantName] = @ConsultantName, [HospitalName] = @HospitalName, [Time] = @Time, [Date] = @Date, [AppointmentStatus] = @AppointmentStatus WHERE [PatientNo] = @PatientNo">
<DeleteParameters>
<asp:Parameter Name="PatientNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ConsultantName" Type="String" />
<asp:Parameter Name="HospitalName" Type="String" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="PatientNo" Type="Int32" />
<asp:Parameter Name="AppointmentStatus" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox3" Name="PatientSurname" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox4" Name="PatientForename" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList1" Name="ConsultantName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList2" Name="HospitalName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList3" Name="Time" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="Textbox2" Name="Date" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DropDownList4" Name="AppointmentStatus" PropertyName="Text" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label1" runat="server" Style="position: relative" Text="Patient No"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox1" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"
Display="Dynamic" ErrorMessage="RequiredFieldValidator" Style="position: relative"
Width="148px">Enter a Patient No</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label6" runat="server" Style="position: relative" Text="Patient Surname"
Width="116px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox3" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="TextBox3"
ErrorMessage="Enter a Surname" Style="position: relative" Width="140px"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label7" runat="server" Style="position: relative" Text="Patient Forename"
Width="128px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox4" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="TextBox4"
ErrorMessage="Enter a Forename" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label2" runat="server" Style="position: relative" Text="Consultant Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
DataTextField="UserName" DataValueField="UserName" Style="position: relative">
<asp:ListItem Selected="True">Select...</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"
SelectCommand="SELECT aspnet_Users.UserName FROM aspnet_UsersInRoles INNER JOIN aspnet_Users ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (aspnet_Roles.RoleName = 'Consultant')"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="DropDownList1"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="152px">Select a Consultant</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label3" runat="server" Style="position: relative" Text="Hospital Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="HospitalName" DataValueField="HospitalName" Style="position: relative">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
SelectCommand="SELECT [HospitalName] FROM [Hospital]"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="DropDownList2"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="136px">Select a Hospital</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label4" runat="server" Style="position: relative" Text="Appointment Date"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:Calendar ID="Calendar1" runat="server" Font-Size="Smaller" Style="position: relative" OnSelectionChanged="Calendar1_SelectionChanged" OnDayRender="Calendar1_DayRender">
</asp:Calendar>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox2" runat="server" Style="position: relative; left: 0px; top: 8px;" Width="256px"></asp:TextBox><br />
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox2"
Display="Dynamic" ErrorMessage="Enter a Date" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px; height: 26px">
</td>
<td style="width: 136px; height: 26px">
<asp:Label ID="Label5" runat="server" Style="position: relative" Text="Appointment Time" Width="132px"></asp:Label></td>
<td style="width: 47px; height: 26px">
</td>
<td style="width: 100px; height: 26px">
<asp:DropDownList ID="DropDownList3" runat="server" Style="position: relative; left: 0px; top: 0px;">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem Value="09:00">09:00</asp:ListItem>
<asp:ListItem>09:30</asp:ListItem>
<asp:ListItem>10:00</asp:ListItem>
<asp:ListItem>10:30</asp:ListItem>
<asp:ListItem>11:00</asp:ListItem>
<asp:ListItem>11:30</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px; height: 26px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="DropDownList3"
Display="Dynamic" ErrorMessage="Select a Time" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px; height: 26px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label8" runat="server" Style="position: relative" Text="Appointment Status"
Width="136px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList4" runat="server" Style="position: relative">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem>Booked</asp:ListItem>
<asp:ListItem>Modified</asp:ListItem>
<asp:ListItem>Patient Notified</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Select a Status"
Style="position: relative" Width="120px" ControlToValidate="DropDownList4"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td align="center" style="width: 100px">
<asp:Button ID="Button1" runat="server" Style="position: relative" Text="Submit" OnClick="Button1_Click1" /></td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr></table>
</asp:Content>
Any help would be very much appreciated.
Thanks,
James.
I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.
Student_name
_____________
Rocky
Albert
Rocky
Williams
Albert
Robert
The query will show
Student_name
______________
Rocky
Rocky
Albert
Albert
I am trying to save user data to a sql table but keep getting the following error;
Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'.
I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows;
Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure
Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString
End Function
Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process.
Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help.
Thanks,
Jaime
Can anyone spare me a good SP that ....
checks if the record exists - and if so return the identity.
and if the record does not exist
inserts it and returns the identity.
Mine keep flipping up :) so I rather look at someone else and use theirs.
Thanks in advance.
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],
[code]...
Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values
Please help
thanks
I just wanted to delete a record from a filetable by "delete from dbo.DocumentStore where stream_id = 'A322276D-AE65-E511-8266-005056C00008'".
Then i received error 1934:
Meldung 1934, Ebene 16, Status 1, Zeile 578
Fehler bei DELETE, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ANSI_PADDING'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierte Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen
und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind.
Something that i made a mistake with the SET-option for 'ANSI_PADDING' with indexted views and/or calculated rows and/or filtered indeces ...
Given the following code, why won't the information that I have at the end of my WHILE loop insert at the end of the table?
For some reason (there are over 7000) records being written to this table, the row that I am trying to insert is always included at row 73.
Any ideas? All I want to do is have it insert at the end of this script at the end of the table.
Thanks,
Steve
ALTER procedure sp_Cigna_build_export
as
Declare CignaExport Cursor
For
Select
exprectype,
expssn,
expfiller,
expbody
From cigna_employee_sort
Declare
@RecordType char(2),
@SSN char(11),
@Filler char(20),
@Body char(867),
@Counter int,
@CountLength int,
@RecordCount char(9)
select @counter = 0
Open CignaExport
Fetch Next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
While @@fetch_status = 0
Begin
select @counter = @counter + 1
insert into cigna_export_table
(exportfield)
select
@recordtype + @SSN + @Filler + @Body
Fetch next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
End
-- Adding two here accounts for the header/trailer records
select @recordtype = '99'
select @counter = @counter + 2
select @RecordCount = '000000000'
select @CountLength = len(@counter)
print @counter
insert into cigna_export_table
(exportfield)
select
@recordtype + stuff(@recordcount,10-@countlength,@countlength,@counter)
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
Hi all,
I Have the following situation:
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL,
titre VARCHAR(50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR(20) NOT NULL,
resume TEXT,
codePays VARCHAR(4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable;
How can I do it in MS SQL or Transact-sql?
Thanx all
How can we retrive record from table those record which are insert,update or deleted on last date.
View 1 Replies View RelatedHi all!
I'm runing an access application that has an sql server 2000 as its back end. Up to few weeks ago everything was fine, but now when a user tries to add a record from an access form into the database I get an error that it cannot insert a null value into the field.
The two fields that cause the problem do not allow nulls as they keep the user name and the time that the record was inserted into the table.
I trace the access program but I cannot pinpoint where the valuees are inserted into the table. All the other fields are bound on the form. When the problem first appeard I found out that at that same day the active directory server that keeps the logins to the network was restarded. The problem appears on the live database that the users use and as well as on my test sql server that is not connected to the main database server but is instaled on my pc together with the access application.
But I still have to login onto the same network to use my pc that is part of the same domain
The authentication on the sql server 2000 is windows only.
Any solutions or directions for this proplems?
Thanks for any help
George
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
I have Table Staffsubjects with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] .....
I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?
ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES
[Code] ....
In SQL SERVER 2005, how can I get the ID of the record I just insert to table?
I defined a table MyTable, and I insert a record into the table using the SQL below
Insert into MyTable (Name) values ("User Name")
You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table?
CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?
I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.
thanks!
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?
View 2 Replies View RelatedI need to find out if a Transaction ID exists in Table A that does not exist in Table B. If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. This would work easily if there were only one row. What is the best way to handle it if there are a bunch of rows? Should there be some type of looping?
View 12 Replies View Related