insert same record twice into one table

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 2000 Forums

SQL Server Development (2000)

insert same record twice into one table

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 01:39:34

I have inserted orderdetails records( total 2157 rows)from an orderdetails table into an orderdetails2 table.
Now, I would like to insert same records from an orderdetail table into an orderdetails2 table after rowcount 2157.
How do i accomplish this using @@rowcount?

Thanks

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-25 : 03:21:41

You need to have something that indicates roworder. (A relational database has no roworder) For example a 'DateInserted' column

If I take this as an example you would get something like

INSERT INTO ....
SELECT * FROM OrderDetails WHERE OrderDetailId NOT IN (SELECT TOP 2157 OrderDetailId FROM OrderDetails ORDER BY DateInserted)

Post your DDL if you need a more specific answer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 09:32:45

Let's use [Order details] table in Northwind DB as an example.

Select * into
test..orderdetails
from Northwind.dbo.[Order Details]

2156 rows inserted

After that

I would like to insert same records again into the test..orderdetails table after row number 2156.

SET ROWCOUNT 2157
WHILE (1=1) BEGIN
Select * into
test..orderdetails
from Northwind.dbo.[Order Details].... something lime that

Like you said, Do i need to created another column called dateinserted(MyLastInserteddate) to insert records after row number 2156.

WHERE
MyLastInserteddate < 'current insert date'

Or using inserted trigger????

Thank you for your help on this problem.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-25 : 09:54:31

quote:
I would like to insert same records again into the test..orderdetails table after row number 2156.

I'm not sure what you mean by this. There's no such things as a row number.

Can you be even more specific on what you want (and maybe why?)

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 10:32:13

I meant 2155 rows ( total number of rows in [order details] table). there are 2155 records in [order details] table in Northwind DB.( select * from [order detail]--> it will return 2155 rows). And, I would like to append same records after row number 2155. So,the total number of records should be 4310.

I know I can do this using UNION ALL. But I would like try it in a diffrent way.( may be using @@rowcount )

Thanks

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-25 : 10:36:23

Like I said before,

there's no such thing as rownumber.

You can't insert something 'after rownumber 2155'.

Just execute the same statement twice, if you want to insert the records twice.

What you want is not possible.

(edit typo)

Edited by - Peter Dutch on 03/25/2003 10:37:18

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-25 : 11:10:05

Oh chill peter

jung1975:

I think the problem here is that you are trying to use the SELECT INTO syntax, which of course creates the table and populates it. What you need is this syntax - INSERT INTO...SELECT..., which Peter has already mentioned in his first post. Or you can use the first to create the table and populate it with the "first 2157" batch and then use the second to add the second batch.

For example:
USE Northwind
SELECT * INTO OrderDetails2 FROM OrderDetails --This creates the table
INSERT INTO OrderDetails2 SELECT * FROM OrderDetails --Repeat this statement as many times as needed

OS

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 14:31:01

YEAH!!

you're right. THERE IS NO SUCH a THING CALLED ROWNUMBER IN SQL SERVER.

.

There is ROWNUM equivalent of count(*) in ORACLE though.

Thanks for wake me up!!!

carrey
Starting Member

22 Posts

Posted - 2003-03-31 : 12:17:33

Not sure why you'd want to do it but, another way to do it would be to insert on a cartesian product e.g

USE Northwind
SELECT 1 as id_cart
INTO #CARTESIAN
UNION
SELECT 2

SELECT o.*
INTO OrderDetails2
FROM OrderDetails o,
#CARTESIAN c

The missing join creates a row for every row in each table i.e in your case 2 * the number of rows in OrderDetails. If you want more rows then insert more rows in CARTESIAN i.e for 4 * the number then insert another 2 rows.

cheers

Edited by - carrey on 04/01/2003 03:53:13

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 12:34:50

Carrey -- I'm a big fan of cartesians for this purpose, as well.

An easier way than using a temp table or another table is:

select YourData
from
YourTable
cross join
(select 1 as a union all select 2 as a) b

I use that technique all the time ...

I do like to specify CROSS JOIN every time i use a cartesian product, though, so people don't think there's a join missing or feel they need to look in the WHERE clause for a possible join.

- Jeff

Edited by - jsmith8858 on 03/31/2003 12:35:50

carrey
Starting Member

22 Posts

Posted - 2003-04-01 : 03:52:53

quote:

I do like to specify CROSS JOIN every time i use a cartesian product, though, so people don't think there's a join missing or feel they need to look in the WHERE clause for a possible join.
- Jeff

Jeff,

I'm using Sybase and AFAIK CROSS JOIN is not supported.
cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources