How to add rows to same table

I have a table of 5 columns (a snippet of the table is shown below). I need to copy the rows where the second column's GUID is for example C7E5BA40-54C9-11D7-83A0-0080C8FC6E95 into the same table but the first and second column GUID must be renamed. I can rename the second column GUID quite easily but how would I rename the first Column's GUID?

This is the sql query that renames the second column and copies into the same table:

string newToolGUID = System.Guid.NewGuid().ToString("B");

insert into strtrigger select @newToolGUID as stoolguid, striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = @selectedToolGUID", connection))

 

The Table:

sTriggerGUID                                                               sToolGUID                                               byttriggertype........bytexpires..............sname

{00FF6615-C8F8-44F0-0877-D7580A628211}........{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..... ....................1..... ....................9 - Project Plan - Milestone due in 1 week
{0205EB7E-77B1-4750-09D4-CF513894F98F}.......{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..........................1..... ....................6 - Project Plan Status Change
{02297153-F8F8-44D1-855F-00DC3100A199}........{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..... ....................1..... ....................6 - Project Plan Status Change
{0270303C-701F-48F2-B342-E7D41EC9C16C}......{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}...... 0..... ....................1..... ....................9 - Project Plan - Milestone due in 1 week

0
InsanePaul
8/22/2008 11:13:02 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

11 Replies
730 Views

Similar Articles

[PageSpeed] 46
Get it on Google Play
Get it on Apple App Store

u question is not clear! can u please post with example data,, i mean desire output  ?

ur trying insert data( in strtrigger ) by select yournewgiveid as stoolguid , striggerguid some values from strtrigger....

why ur renaming the column in select list ?

seems to me your r altering the columns data with new value ... is it ?

then try

insert into strtrigger(sTriggerGUID, sToolGUID,byttriggertype, bytexpires, sname)

select @newToolGUID, striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = @selectedToolGUID

 


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/23/2008 6:26:57 AM

Hi,

    If you insert the same data into the table by changing first two columns with guids, then you can use following code:

insert into strtrigger

select newid() as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = 'id you to insert into table'

Newid() is a function in SQL Server which generated uniquie guid everytime for each row.


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
8/23/2008 11:54:54 AM

Hi,

What you say makes sense. I tried your line of code in SQL but I get a foreign key constraint conflict. The conflict occurs in the parent table where 'stoolguid' is the primary key.

I used an actual GUID since the GUID needs to be in the parent table.

The parent table contains stoolguid '{000000bd-a7ad-4d5f-a01e-393803267ba5}'  and the strtrigger table contains  '{9E40B1F5-7187-4B63-8949-985571B06E97}' so I think I've covered everything but still getting the conflict. Any ideas what I need to do?  

insert into strtrigger 
select '{006b35bd-a7ad-4d5f-a01e-393803267ba5}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname 
from strtrigger 
where stoolguid = '{9E40B1F5-7187-4B63-8949-985571B06E97}'
0
InsanePaul
8/26/2008 9:05:29 AM

"u question is not clear! can u please post with example data,, i mean desire output  ?"

 Hi, the other person's reply I think is the answer but I get a foreign key conflict with the parent table whose primary key is stoolguid. But I ensured that the new stoolguid in the strtrigger table was also in the parent table (so keeping referential integrity) so dont know what i have done wrong.

The Table:

sTriggerGUID                                                               sToolGUID                                               byttriggertype    bytexpires                      sname

{00FF6615-C8F8-44F0-0877-D7580A628211}........{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..... ....................1..... ....................9 - Project

{0205EB7E-77B1-4750-09D4-CF513894F98F}.......{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..........................1..... ....................6 - Project Plan

{02297153-F8F8-44D1-855F-00DC3100A199}........{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}..... 0..... ....................1..... ....................6 - Project Plan

{0270303C-701F-48F2-B342-E7D41EC9C16C}......{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}...... 0..... ....................1..... ....................9 - Project Plan

 The Result after sql query (striggerguid must be different in each row)

insert into strtrigger select '{006b35bd-a7ad-4d5f-a01e-393803267ba5}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = '{C7E5BA40-54C9-11D7-83A0-0080C8FC6E95}'

sTriggerGUID                                                               sToolGUID                                               byttriggertype    bytexpires                      sname

{111115bv-v8fg-f56f-36548632b5}........{006b65bv-v8fg-f56f-36548632b5}..... 0..... ....................1..... ....................9 - Project

{222225bv-v8fg-f56f-36548632b5}.......{006b65bv-v8fg-f56f-36548632b5}..... 0..........................1..... ....................6 - Project Plan

{333335bv-v8fg-f56f-36548632b5}........{006b65bv-v8fg-f56f-36548632b5}..... 0..... ....................1..... ....................6 - Project Plan

{444445bv-v8fg-f56f-36548632b5}......{006b65bv-v8fg-f56f-36548632b5}...... 0..... ....................1..... ....................9 - Project Plan

 

Please can you suggest what is wrong

Many thanks

0
InsanePaul
8/26/2008 9:20:20 AM

insert into strtrigger
select newid(),'006b35bd-a7ad-4d5f-a01e-393803267ba5', byttriggertype, bytexpires, sname from strtrigger where stoolguid = 'C7E5BA40-54C9-11D7-83A0-0080C8FC6E95'

if it dosen't solved your issue

create script  table and the insert scripts and post here .. so that we can test it in our local machine


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 9:57:30 AM

The script for the 2 tables are:

USE [ST2GO]
GO
/****** Object:  Table [dbo].[strTool]    Script Date: 08/26/2008 11:07:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[strTool](
[sToolGUID] [nvarchar](50) NOT NULL,
[sName] [nvarchar](100) NULL,
[sDescription] [nvarchar](255) NULL,
[dtmLastSavedDate] [smalldatetime] NULL,
[sPartnerOwner] [nvarchar](100) NULL,
[bytTriggersEnabled] [tinyint] NULL,
[sToolConceptHelp] [ntext] NULL,
[bArchive] [tinyint] NULL,
CONSTRAINT [PK_strTool] PRIMARY KEY CLUSTERED
(
[sToolGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

 
USE [ST2GO]
GO
/****** Object:  Table [dbo].[strTrigger]    Script Date: 08/26/2008 11:08:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[strTrigger](
	[sTriggerGUID] [nvarchar](50) NOT NULL,
	[sToolGUID] [nvarchar](50) NOT NULL,
	[bytTriggerType] [tinyint] NOT NULL,
	[bytExpires] [tinyint] NOT NULL,
	[sName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_strTrigger] PRIMARY KEY CLUSTERED 
(
	[sTriggerGUID] 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
ALTER TABLE [dbo].[strTrigger]  WITH CHECK ADD  CONSTRAINT [FK_strTrigger_strTool] FOREIGN KEY([sToolGUID])
REFERENCES [dbo].[strTool] ([sToolGUID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[strTrigger] CHECK CONSTRAINT [FK_strTrigger_strTool]
 The insert statement i use is:

insert into strtrigger select '{006b35bd-a7ad-4d5f-a01e-393803267ba5}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = '{9E40B1F5-7187-4B63-8949-985571B06E97}'

If I use just the 'select' part of the above 'insert' statement, the resulting table is what I want to insert

0
InsanePaul
8/26/2008 10:14:34 AM
where is the create table script strTool

where is sample data insert sql statement for strTrigger,strTool Angry



Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 10:37:05 AM

Hi, 

I don't use SQL all that much and misunderstood what you wanted. My last post included the script for both strTool and strTrigger unless I'm reading it wrong.

Here is a couple of inserts for the strTool table

insert into strtool (stoolguid,sname, sdescription) values ('{000005bd-a7ad-4d5f-a01e-393803267ba5}', 'Project 1', 'This is a project')

insert into strtool (stoolguid,sname, sdescription) values ('{000005bd-a7ad-4d5f-a01e-393803267ba6}', 'Project 2', 'This is a project 2')

 

When I try to insert values in the strtrigger I get the same foreign key constraint conflict. I tried 3 different inserts and have highlighted the differences in the value

insert into strtrigger (striggerguid, stoolguid, byttriggertype, bytexpires,sname) values ('{0005BA40-54C9-11D7-83A0-0080C8FC6E95}','000005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )

insert into strtrigger (striggerguid, stoolguid, byttriggertype, bytexpires,sname) values ('{1235BA40-54C9-11D7-83A0-0080C8FC6E95}','000005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )

insert into strtrigger (striggerguid, stoolguid, byttriggertype, bytexpires,sname) values ('{1235BA40-54C9-11D7-83A0-0080C8FC6E95}','123005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )

0
InsanePaul
8/26/2008 11:09:23 AM

select * from strTool

{000005bd-a7ad-4d5f-a01e-393803267ba5}    Project 1    This is a project    NULL    NULL    NULL    NULL    NULL
{000005bd-a7ad-4d5f-a01e-393803267ba6}    Project 2    This is a project 2    NULL    NULL    NULL    NULL    NULL

-- Your Insert Command

insert into strtrigger
(striggerguid, stoolguid, byttriggertype, bytexpires,sname)
values
('{0005BA40-54C9-11D7-83A0-0080C8FC6E95}','000005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )

return's error:

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_strTrigger_strTool'. The conflict occurred in database 'Northwind', table 'strTool', column 'sToolGUID'.
The statement has been terminated.
 

if this is the error then try this 

insert into strtrigger
(striggerguid, stoolguid, byttriggertype, bytexpires,sname)
values
('{0005BA40-54C9-11D7-83A0-0080C8FC6E95}','{000005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )

insert into strtrigger
(striggerguid, stoolguid, byttriggertype, bytexpires,sname)
values
('{1235BA40-54C9-11D7-83A0-0080C8FC6E95}','{000005bd-a7ad-4d5f-a01e-393803267ba5}',0,1,'hello' )
 

select * from strTrigger

{0005BA40-54C9-11D7-83A0-0080C8FC6E95}    {000005bd-a7ad-4d5f-a01e-393803267ba5}    0    1    hello
{1235BA40-54C9-11D7-83A0-0080C8FC6E95}    {000005bd-a7ad-4d5f-a01e-393803267ba5}    0    1    hello

 

 


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 11:36:49 AM

Yes I see that I didn't use the bracket which is why i couldn't insert data. So it solves that particular problem. The original problem gives me the same error "Server: Msg 547, Level 16, State 1, Line 1" but the statement below as far as I can tell is correct:

insert into strtrigger select '{9E40B1F5-7187-4B63-8949-985571B06E97}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = '{006b35bd-a7ad-4d5f-a01e-393803267ba5}'

Any idea whats wrong?

0
InsanePaul
8/26/2008 12:29:54 PM

take a look at you query  

insert into strtrigger select '{9E40B1F5-7187-4B63-8949-985571B06E97}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = '{006b35bd-a7ad-4d5f-a01e-393803267ba5}'

 did u specify any sequence of column names for the table ? no ... so it will try to insert data according to your table definition's sequence

ie striggerguid then stoolguid and so on .....

here ur using the select statement to insert the data and ur not specifying  the  column order ..so  it will try to insert newid() data  to the stoolguid column(as ur specifying the newid to the second column whether ur renaming the column name or not in the select list ) and which is not present in your original table  causing the referential integrity to prepare error message

 

try with this

insert into strtrigger(stoolguid,striggerguid,.......so on... column name)

select '{9E40B1F5-7187-4B63-8949-985571B06E97}' as stoolguid, newid() as striggerguid, byttriggertype, bytexpires, sname from strtrigger where stoolguid = '{006b35bd-a7ad-4d5f-a01e-393803267ba5}'

if it gets the same error then try to put { } in the newid() .. some thing like '{'+newid()+'}'  

 


Thanks,
Kamrul Hassan

please mark as answer if it helps u.
0
kamrul3d
8/26/2008 7:39:05 PM
Reply:

Similar Artilces:

Add row to table with table.row.add()
Everytime I click on a button I want to add a tableRow to a table like this:        TextBox TB = new TextBox();                TableCell Cell = new TableCell();        Cell.Controls.Add(TB);        TableRow Row = new TableRow();        Row.Cells.Add(Cell);        table.Rows.Add(Row);It works the first time, but when I click the button again the last r...

how can i add row to table of sql with vb.net code?
how can i add row to table of sql with vb.net code?  You can use System.Data.SqlClient classes :  Using con As New SqlConnection(" your connection string goes here ") Dim com As New SqlCommand("Insert into Person (id,name) values(@id,@name) ", con) com.Parameters.AddWithValue("@id", 1) com.Parameters.AddWithValue("@name", "Nomi1") com.Connection.Open...

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

Best way to add a row to a SQL Server table?
What is the best way to add a row to a SQL Server table?  Assume that stored procedures aren't an option.  There are 3 methods I can think of using ADO .Net:  1.  Execute an Insert statement string e.g. "Insert into Books (Field1, Field2) Values ('" & myval.replace("'","''") & "', '" & myval2.replace("'","''") & "')"  As long as you double any single quotes in the values, this method seems fine. 2. Paramterized Insert statement  ...

How to add new row to datagrid like in the SQL server when open table and enter data
hello mate!,         could you please tell me how can i achieve a datagrid that adds a new row when ever we click on the first of of the last row.(like in the sql server management studio 2005 in which when we right click a particular table and click open table and enter data beyond the page size of the datagrid) Thanks,Much Obliged & Thanks a Lot,Myself Try: http://geekswithblogs.net/casualjim/articles/51360.aspxCheers,ChrisRemember, you can mark more than 1 post as the answer....

Add a row to table Dynamically for each click of Add Button
Dear All, I need to add one row to the existing table each time when I click Add button. I tried with the below code. It adds one row for the first click and for the next clicks it does nothing.. could you please recify where I am wrong? private void Button1_Click(object sender, System.EventArgs e){     int cnt = Convert.ToInt32(Session[COUNT]) + 1;    HtmlTableRow tRow1 = new HtmlTableRow();tRow1.ID = "rowID" + cnt;tblCR.Rows.Add(tRow1);HtmlTableCell tCel1 = new HtmlTableCell();tCel1.InnerText  = "aaa";tRow1.Cells....

How to add a footer row to a table after deleting all footer rows
I'm sure this is a very easy thing to do, but I can't figure out how to do it. I have a table, and I deleted all the footer rows.  Now I would like to add one footer row.  How do I do that? If I right-click on the detail row and select Insert Row Below, it is, of course, another detail row. I'm stumped -- thanks for any help you can give!Cynthia Do you have groups in your table?  You should be able to right click on any table row and toggle the corresponding menu item to show the table header, details or footer.  It is in the right click menu includ...

Trying to append (add) row to table: row overwritten :(
 Hi, I was hoping someone could lend me a hand. I have a button and want to add one row to a table every time I click that button. The thing is, it works find the first time, but the second, third, ... the row is simply overwritten. Does anyone know what I'm doing wrong? I simply want to append a new row to the end of the table. This is the C# code:1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 TableRow row = new TableRow(); 4 5 for (int i = 0; i < 5; ++i) 6 { 7 TableCell cell = new TableCell()...

adding html table columns from SQL table rows
I want to put data from an SQL table into an HTML table, but I want to start a new row in the HTML table when it is 5 columns wide. I have done this in classic ASP with the following code: TABLE NAME How can I accomplish the same thing with asp.net? I have created my SqlDataSource and the SelectCommand no problem, but I'm kind of stuck how to loop through the recordset. Why do you want to do it manally? Data binding is for such purposes only. Fill a DataSet or DataTable with the SqlCommand and do: GridView.DataSource = DataSet.Ta...

Using PageTemplate to have a row used to add new rows to the datasource
I read in the Programming Microsoft ASP.NET 2.0 book that you can use the PagerTemplate to have an "extra" row used to add new rows to the datasource bound to the GridView.  Unfortuantely, it does not show how. I am trying to do this and I am having a problem where the Pager row is not showing up. I have defined a template:<PagerTemplate> <table width="100%"> <tr> <td><asp:LinkButton ID="btnAddFMEff" runat="server" Text="Add" CommandName="Add" /></td> <td style="width: 80%;"><asp:TextBox ID="txtNewEffect" runat="se...

add data from multiple rows in table to one row in gridview
 heya is there any way of adding data from multiple rows in table to one row in gridview. eg. ipc                 column 1 ,            2 ,                       3,                 4  1,    11111                 33                  &nbs...

Run sql script to add tables to Sql 2005 Express
Is it possible to run a sql installation script from Visual Studio 2005 and populate a Sql Server 2005 Express database? To save time (and error) I have made a sql script for creating tables and stored procedures for my web site. I could run this script from the Sql Server 2005 Express Management tool, but this tool does something to the database that result in the error "Cannot open user default database. Login failed.". After hours searching on the net I found a tip warning me not to use use the Management tool. So, are there other ways to run my sql script?Regards, Sigurd  ...

ADO.net problem: i can not view image data for the rest of my table rows except the image for the first row but i can read all the other non image fields for all rows.
Hi i have a table with the following fields imgid............int imgdata........image imgtitle.........varchar(max) imgType........varchar(max) imgLength.....bigint incident_id.....int i have no problem with uploading the image but viewing the uploaded image(s) in a gridview has caused me great pain. The probelm is i have two pages. page1 grabs the image to display and page2 has a gridview that displays the grabed image. I wanted to have an sql statement like SELECT * FROM mytable WHERE incident_id=someValue. IF i have 5 records in this table and i view page2 ...

SQL Old school *= Question select All rows from table A and rows from B
Im using SQL 2005. Love it.In the old TSQL world could useselect current.user, weekly.user, weekly.hoursfrom current, weeklywhere current.user *= Weekly.user and get all the current users from current table AND users from weekly table that have weekly hours. Somehitng like dan  12fred 24sue nullwhere sue is in the current table, but not in the weekly table.I've tried outside joins like this SELECT     dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorkedFROM         dbo.ViewCurrentUser LEFT OUTER JOIN&n...

Need to add a row of data in table B for each person listed in table A
I need to figure out how to do a "mass" insert into a sql table.   From Table A, I have each users USERID,  I need to grab that UserID and use it to insert a row into Table B with the new row containing the USERID and a Desc.  Here's how my data looks to do the select.. but how do I write the Insert?  (Select USERID from TableA) 71233 (Insert into TableB, USERID and Desc) USERID      DESC7                XXX12       ...

Web resources about - How to add rows to same table - asp.net.sql-datasource

Table tennis - Wikipedia, the free encyclopedia
Table tennis , also known as ping-pong , is a sport in which two or four players hit a lightweight, hollow ball back and forth using table tennis ...

Everything is on the table: leaked COAG agenda reveals GST changes being considered
Massive increases to the GST that would raise up to $45 billion annually will be on the table when Malcolm Turnbull and state premiers meet on ...

Off the table: why the GST won't be lifted to 15 per cent
So out of favour is the idea of increasing the GST that by the time the government releases its tax options paper in the new year a 15 per cent ...

Philly Papers Set the Kobe Table
As usual, the Philadelphia Daily News has the killer-pun front page headline. But in terms of must-reads ahead of Kobe Bryant’s final visit ...

A Star Destroyer on your table: Ars reviews all three Star Wars miniatures games
Ars Cardboard reviews the Star Wars miniatures games X-Wing, Armada, & Imperial Assault.

Laying My Cards on the Table
Steven Hayward at the wingnut PowerLine blog indulges in a silly hypothetical: GUNS: TIME TO CALL THE LIBERAL BLUFF? Here’s an idea: let’s ...

Connecting the Online World to the Offline World with Data: A Round-table Chat at #BIAKNEXT
Connecting the Online World to the Offline World with Data: A Round-table Chat at #BIAKNEXT was originally published on BruceClay.com, home of ...

Star Trek Transporter Pad Coasters Help Your Wooden Tables Live Long and Prosper
... simple tool that makes life more efficient, cutting down on the need for extra time spent cleaning while extending the lifespan of wooden tables. ...

Take money off the table: Yamada:
The S&P 500 just saw its first two-day winning streak in over a month, but Louise Yamada warns of disturbing price action.

McDonald’s Offering Table Service, New Menu At California Restaurants
... method has worked for decades, the Golden Arches is apparently ready to change things up and show its fancier side by testing table service ...

Resources last updated: 12/16/2015 6:40:42 PM