MS SQL Server :: Insert Records From Same Table

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting

On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??

<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">

<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>

</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"

ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>

</asp:SqlDataSource>
</asp:WizardStep>

Event Handler

To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();

}

View 1 Replies View Related

Transact SQL :: Insert Records From Two Table Into A Table

Sep 20, 2015

Lets say we have two tables

tblPayments (Contains the records of Payments we made)

ID DATE AMOUNT BANK
----------------------------------------------------------
1 05/05/2015 5000 Natwest
2 05/05/2015 2000 Lloyds
3 05/06/2015 3500 Natwest
4 05/07/2015 4000 Natwest
5 05/08/2015 1500 Lloyds

tblReceipts (Contains the records of Receipts we received)

ID DATE AMOUNT BANK
----------------------------------------------------------
1 05/06/2015 5000 Natwest
2 05/06/2015 2000 Lloyds
3 05/07/2015 3500 Natwest
4 05/07/2015 4000 Natwest
5 05/08/2015 1500 Lloyds

Now, I also have a blank table (tblBankStatement) which contain the following columns

ID DATE RECEIPT PAYMENT BANK
-----------------------------------------------------------------------------

I want that when I execute the query, the query should INSERT the records to the New Table (tblBankStatement) from
tblPayments and tblReceipts by Date Ordered in ascending way WHEREBank should be 'Natwest'.

Also the Amount Column Data in tblPayments should be Inserted into the Payment Column in tblBankStatement and the Amount Column Data in tblReceipts should be Inserted into the Receipt Column in tblBankStatement.

So I could get the data just like below

tblBankStatement

ID DATE RECEIPT PAYMENT BANK
--------------------------------------------------------------
1 05/05/2015 0.00 5000 Natwest
2 05/06/2015 0.00 3500 Natwest
3 05/06/2015 5000 0.00 Natwest
4 05/07/2015 0.00 4000 Natwest
5 05/07/2015 4000 0.00 Natwest

What query should I write to perform the task above.

View 4 Replies View Related

How To Insert Multiple Records Into Table

Oct 11, 2007

insert into table1 (colname) values (value1)
can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?
We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take
value1, value2, value3 ....
from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

View 5 Replies View Related

Updated Records From A Table Insert Into Another

Jul 26, 2007

Hi:

I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level.
I have no experience with it and any precise hints/solutions would be very appreciated

selmahan

View 1 Replies View Related

Trigger To Insert Records In The Same Table?

Dec 23, 2011

we have various user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)

This field is a drop down list , after insert committed , i want other user defined field to populate.

Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be populated.

CREATE TRIGGER User_Defined_field_KAE
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER
INSERT

AS

INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Type_Id,
name
SELECT
Client_Id,
'1089',
A.USER_PHONE_1
FROM inserted , [dbo].[ADMN_User_Details] as A
WHERE name = a.user_id;

View 11 Replies View Related

Insert Records When Not In Target Table

Dec 29, 2014

I have need to insert records when not in the target table.

sample ddl for source and target tables. Notice the target table is missing the 'Upstairs' Rows:

CREATE TABLE source (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)

[code]....

View 2 Replies View Related

Insert Records Into A Table With Foreign Keys

May 21, 2008

i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.

View 5 Replies View Related

How To Insert Multiple Records Into Sql 2000 Table At Once ?

Nov 25, 2004

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
---order no ----orderno
---issuedate ----itemname
---supplier ----desccription
---amount -----price
----Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!

View 2 Replies View Related

SQL 2012 :: How Many Records Can Insert Into A Temp Table

Mar 25, 2014

I use code below to insert data into a temp table.

How many records can insert into a temp table?

Select * into #temp from ORDER

View 3 Replies View Related

Bcp To Insert The Records From .dbf File Into A Sql Database Table

Apr 12, 2006

Hi

i need to inser the .dbf file records into sql table.

How should i go about it?

regards,

Kiran

View 6 Replies View Related

Transact SQL :: Insert Records To A Table From Different Tables

Aug 30, 2015

Lets say we are executing this query below to retrieve each customer and the amount associated to a table

"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE - Inbound END) AS AMOUNT from
tblSaleStatementCustomer WHERE CustomerType = 'Dealer' GROUP BY CustomerName"

Which display the data like below

DEALER AMOUNT
------------------------------------------------
ABC 2000
XYZ 1000

However I have one more table TABLE2 which contains two columns

DEALER OPENING
-------------------------------------------------------
ABC 5000
XYZ 7000

I want to modify my query in such a way that AMOUNT column should also add

OPENING from TABLE2 So that I must get the result like below

DEALER AMOUNT
------------------------------------------------
ABC 7000
XYZ 8000

View 10 Replies View Related

Can I Insert Records Into A Table From A Stored Procedure

Oct 9, 2007

I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.

I use the logic in other sprocs too.

Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....

INSERT INTO Insurance (Name)
Exec sp_AddNamesList

Thanks,

Mike

View 5 Replies View Related

Insert Multiple Records Into A Single Field On Another Table

Feb 15, 2012

I have a table JOBCODE which contains a list of codes.

I want to insert these values into table VIEWS as a list separated by spaces.

E.G.

Table Jobcodes looks like this

code
1
2
3
4
5
6

And I want table Views to look like this:

field1
1 2 3 4 5 6

How do I go about this?

View 4 Replies View Related

Query To Insert Records Into A Table Based On Request

Jul 31, 2014

I have a query to insert records into a table based on a request but the query can only read one record at a time. How do i change the query such that it is able to read multiple records. In the below query i was able to input only 1 request which is 149906.

Query

declare @num_of_times int
declare @Count INT
DECLARE @newrequestid varchar(50)
DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INT
SET @RequestId = 149906
SET @x = 1

[Code] ....

View 1 Replies View Related

SQL Server 2014 :: Add Multiple Records To Table (insert / Select)?

Jul 31, 2014

I am trying to add multiple records to my table (insert/select).

INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name

But I get the error:

Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.

But I am using the max User_id + 1, so it can't be duplicate

This would insert about 20 records.

Why the error?

View 7 Replies View Related

Writing Trigger To Insert Records Into Master And Child Table At A Time ?

Oct 17, 2007

I am developing an application in vb.net 2005 using SQL Server 2000.
In this I have two tables SessionMaster and SessionChild.
Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type,
Fields of session child - SessionChildId, SessionMastId, UserName, Comment.
SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields.
Please how to write trigger to insert record into both tables at a time.

View 2 Replies View Related

SQL 2012 :: Snapshot Getting Corrupted After Insert Update Few Million Records Into A Table

Mar 12, 2015

We are facing a weird scenario in which the snapshot is getting corrupted after insertupdate few million records in to a table .

SQL Server 2012
windows server 2008 R2
service pack 1
64-bit OS

View 1 Replies View Related

SQL Server 2012 :: How To Quickly Update / Insert 3M Records In Large Table

Mar 28, 2015

Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.

The initial solution is:

1 Create a table (table_b) which structur is as the same as table_a

2 Use BCP to import updated records into table_b

3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields

4 Append updated or new data into table_a:
insert into table_a select * from table_b

As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?

View 9 Replies View Related

SQL Server 2012 :: Set Based Method To Insert Missing Records Into Table - Right Join Not Work

Apr 24, 2014

I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)

Table A could look like the following where the multiple employees could have multiple types and multiple stages.

EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2

Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.

Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4

So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.

I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.

I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.

My count would look something like this:

EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2

So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.

The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.

View 9 Replies View Related

Data Access :: Bulk Fetch Records And Insert / Update Same In Other Table With Some Business Logic

Apr 21, 2015

I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?

View 14 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

Data Flow Task To Delete Records And Then Insert Records In Transaction

Aug 6, 2007

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
Sunil

View 7 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.

View 4 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table

Oct 21, 2015

I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.

SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY

[Code] ...

It's only returning two rows (the students where they have a row in the HomeworkLogLine table).

View 3 Replies View Related

Insert 9900 Records Out Of 10000 Records Using DTS

Nov 28, 2005

I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.

View 1 Replies View Related

How Do I Select All Records In One Table That Have NO Related Records In Another Table?

Jul 20, 2005

I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks

View 2 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.

In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously

While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.

Is there any SQL Server hints to avoid blocking ..

View 3 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Insert Records

Jul 31, 2002

Sir,

I. which one is better sir amoung the 2 ?

1. Inserting records from front end using begin trans ... commit trans
2. By using stored procedures ? - Is there any begin trans .. commit
trans in stored procedure ? If so how to use it.

please give your valuable suggestions.

View 1 Replies View Related