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.
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, notesand 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, extrawhere TableA.cID = TableB.cIDand place it at the end of the table with the same @cIDany 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 = SomeValueIf 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 = SomeValueTara Kizer |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-05 : 17:19:46
|
INSERT INTO TableB(cID, extra)SELECT cID, extraFROM TableAWHERE cID = SomeValueTo auto-populate SomeValue with the previous queries value, you can use SCOPE_IDENTITY()DECLARE @i intINSERT INTO TableA(ad, notes)SELECT ad, notes FROM TableA WHERE cID = SomeValueSET @i = SCOPE_IDENTITY()INSERT INTO TableB(cID, extra)VALUES(@i, 'SomeValue')Tara Kizer |
|
|
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 intINSERT INTO clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)SELECT *FROM clientcontractdetailWHERE clientcontractdetailid = @ccdidSET @ccdid = SCOPE_IDENTITY()INSERT INTO clientcontractdetaildisplaytype ( clientcontractdetailid)VALUES( @ccdid) |
|
|
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 clientcontractdetailWHERE clientcontractdetailid = ...Tara Kizer |
|
|
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 |
|
|
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)ASDeclare @ccdid intINSERT INTO clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)SELECT *FROM clientcontractdetailWHERE clientcontractdetailid = @SomeVarSET @ccdid = SCOPE_IDENTITY()INSERT INTO clientcontractdetaildisplaytype ( clientcontractdetailid)VALUES( @ccdid)GOThen when calling the stored procedure, you pass in the value you want to copy:EXEC dbo.SomeProc @SomeVar = 2Tara Kizer |
|
|
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 intset @somevar = 1876insert into clientcontractdetaildisplaytype select *from clientcontractdetaildisplaytype (clientcontractdetaildisplaytypeid, clientcontractdetailid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)where clientcontractdetailid = @somevarinsert into clientcontractdetailregionsubselect * from clientcontractdetailregionsub (clientcontractdetailid, regionsubid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)where clientcontractdetailid = @somevarDeclare @ccdid intinsert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)select *from clientcontractdetailwhere clientcontractdetailid = @ccdidset @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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 13:32:56
|
Your code is still not correct:Declare @ccdid intinsert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)select *from clientcontractdetailwhere clientcontractdetailid = @ccdidYou 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 |
|
|
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 questionsAll the columns are in an early post. |
|
|
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 beforeBut you could artificially make it look like it...at the cost of performance perhapsEDIT: Can you just tell us in business terms what you are trying to do and forget the "logic" for nowBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 |
|
|
|
|
|
|
|