Double Inserts 1

[rstum2005]

Just a quick question, I am trying to insert after another but I want them to be on the same rows.

For Example,

Insert into test (FILECODE) select filecode from tester
Insert into test (KEYCODE) select keycode from tester

When I do the above I get this...

FILECODE KEYCODE
a
a
a
a
         B
         B
         B
         B

I do not however want them to be on the same line of code.
I know I can do this but this is not what im trying to do.
I was thinking of using a row indentifier maybe and going by the record number?

Insert into test (FILECODE,KEYCODE) select filecode,keycode from tester

Thanks.

"Success consists of going from failure to failure without loss of enthusiasm."
Winston Churchill~

 

[hmckillop]

You cant do two inserts into one row, one line would be an insert the other would be an update.
Each INSERT , inserts a new row and thus you have multiple rows.
If you know you will only insert one row from the SELECT statement then you could do something like this.
Create an identity field on the test table then use SCOPE_IDENTITY() function to get it to use for the second update

declare @id integer
Insert into test (FILECODE) 
SELECT TOP 1 filecode FROM tester

SET @id = SCOPE_IDENTITY()

UPDATE test SET KEYCODE = (select top 1 keycode from tester)
WHERE IdField = @id

But as you do an insert based on a select statement, you will possibly have multiple inserts.

If you explain in English the purpose you are trying to achieve I am sure you will get a better solution than what I propose

"I'm living so far beyond my income that we may almost be said to be living apart

 

[rstum2005]

hmckillop, thank you for the wonderful reply. I did not know what SCOPE_IDENTITY() was and thus I will look into that and try to complete my task. What I am actually doing is writing a C# application and this part of it is taking a table and inserting it into another. By doing this I have tried to create a loop or array to put these items into an existing table using an insert query. I got as far as going through the array or loop and inserting the items like ive said above. Thanks again hmckillop.




"Success consists of going from failure to failure without loss of enthusiasm."
Winston Churchill~

 

[rstum2005]

Yea I checked to see if it works and it gave me the following error. This error occured when I tried to insert more than the one row.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I suppose Ill keep trying.

Thanks Again.

"Success consists of going from failure to failure without loss of enthusiasm."
Winston Churchill~

 

[rstum2005]

Actually I figured it out. I think what I will do is insted of taking the items and one by one trying to do an insert which is not working anyways. I think I will place the items into an array and then put them into a hidden textbox and placing a comma between each one at the same time which will then create an entire string of the fields in which I want to insert. Geez! why didnt I think of this before....

thanks again!

"Success consists of going from failure to failure without loss of enthusiasm."
Winston Churchill~