Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 copy row from table and put at the end

Author  Topic 

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-05 : 16:54:00
I want to copy a row from TableA which has 3 columns: cID, ad, notes
and place it at the end of the table with a new cID (we will call it @cID)

Then I want to copy a row from TableB which has 3 columns: bID, cID, extra
where TableA.cID = TableB.cID
and place it at the end of the table with the same @cID


any help would be great

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:08:36
There is no such thing as putting rows at the end. The order of data in a table is meaningless.

If cID is an identity, then you can do this:
INSERT INTO TableA(ad, notes)
SELECT ad, notes
FROM TableA
WHERE cID = SomeValue

If cID is not an identity, then you can do this:
INSERT INTO TableA(cID, ad, notes)
SELECT cID = (SELECT MAX(cID) + 1 FROM TableA), ad, notes
FROM TableA
WHERE cID = SomeValue


Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-05 : 17:10:45
So the cID is the next used number for example if a table has 318 cID's the new cID would be 319 and what about the second part?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:14:13
If cID is an identity column, then SQL Server will automatically give it the next number. So is your column an identity column?

Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-05 : 17:16:47
yes it's an identity column...the first query you wrote will work its the next part of my question that is giving me troubles
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:19:46
INSERT INTO TableB(cID, extra)
SELECT cID, extra
FROM TableA
WHERE cID = SomeValue

To auto-populate SomeValue with the previous queries value, you can use SCOPE_IDENTITY()

DECLARE @i int

INSERT INTO TableA(ad, notes)
SELECT ad, notes
FROM TableA
WHERE cID = SomeValue

SET @i = SCOPE_IDENTITY()

INSERT INTO TableB(cID, extra)
VALUES(@i, 'SomeValue')


Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-05 : 17:35:46
I know you are on the right track, so I am including the actual query I have, which is wrong. Help?


/*
INSERT INTO clientcontractdetaildisplaytype
SELECT *
FROM clientcontractdetaildisplaytype (clientcontractdetaildisplaytypeid, clientcontractdetailid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
WHERE clientcontractdetailid = @ccdid
*/


Declare @ccdid int


INSERT INTO clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
SELECT *
FROM clientcontractdetail
WHERE clientcontractdetailid = @ccdid

SET @ccdid = SCOPE_IDENTITY()

INSERT INTO clientcontractdetaildisplaytype ( clientcontractdetailid)
VALUES( @ccdid)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-05 : 17:56:23
The problem is in your first query. @ccdid hasn't been populated, so you can't use it in that INSERT statement. What row do you intend on duplicating:

SELECT *
FROM clientcontractdetail
WHERE clientcontractdetailid = ...

Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-06 : 13:04:57
it is going to be a stored procedure and the row that will be copied will vary
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 13:17:07
That's fine. You still need to pass it a value though. You can't use @ccdid is my point. @ccid doesn't have a value yet, so your query isn't find the row. You need to use another variable and pass to it the row you want to copy.

CREATE PROC SomeProc
(@SomeVar int)
AS

Declare @ccdid int


INSERT INTO clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
SELECT *
FROM clientcontractdetail
WHERE clientcontractdetailid = @SomeVar

SET @ccdid = SCOPE_IDENTITY()

INSERT INTO clientcontractdetaildisplaytype ( clientcontractdetailid)
VALUES( @ccdid)

GO

Then when calling the stored procedure, you pass in the value you want to copy:

EXEC dbo.SomeProc @SomeVar = 2

Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-06 : 13:24:02
Alright I have everything the way it should be. I never did mention that I would be doing this to one more table. It shouldn't make a difference, but I get an error message. I havent set it as a stored procedure yet for testing purposes. I set @somevar to an integer like so:

declare @somevar int
set @somevar = 1876

insert into clientcontractdetaildisplaytype
select *
from clientcontractdetaildisplaytype (clientcontractdetaildisplaytypeid, clientcontractdetailid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
where clientcontractdetailid = @somevar

insert into clientcontractdetailregionsub
select *
from clientcontractdetailregionsub (clientcontractdetailid, regionsubid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
where clientcontractdetailid = @somevar

Declare @ccdid int


insert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select *
from clientcontractdetail
where clientcontractdetailid = @ccdid

set @ccdid = SCOPE_IDENTITY()

insert into clientcontractdetaildisplaytype (clientcontractdetailid)
values(@ccdid)

insert into clientcontractdetailregionsub (clientcontractdetailid)
values(@ccdid)

But I get this error message????

'clientcontractdetaildisplaytypeid' is not a recognized OPTIMIZER LOCK HINTS option.

I really appreciate your help
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 13:32:56
Your code is still not correct:

Declare @ccdid int


insert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select *
from clientcontractdetail
where clientcontractdetailid = @ccdid

You can't use @ccid yet. It hasn't been populated with any values.

Perhaps you should explain in words what you are trying to do rather than just posting code. Please include a data example of all tables involved that would illustrate the goal.


Tara Kizer
Go to Top of Page

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-06 : 14:14:00
Alright I'll try. I want to put a button that says copy which copies the information of that page. The info that is being copied comes from 3 tables and I need to make a procedure that will do this. I'm not sure if that makes sense to you, feel free to as any questions

All the columns are in an early post.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 14:15:41
quote:
Originally posted by tkizer

There is no such thing as putting rows at the end. The order of data in a table is meaningless.



I've heard that somewhere before

But you could artificially make it look like it...at the cost of performance perhaps

EDIT: Can you just tell us in business terms what you are trying to do and forget the "logic" for now





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 14:16:48
Please include a data example of all tables involved that would illustrate the goal.


Tara Kizer
Go to Top of Page
   

- Advertisement -