Inserting Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Inserting Problem

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 02:41:44

I am havig problem in inserting records into different columns of a table.
i execute two queries which are
INSERT Randomtwo(Recordnum1,Record_id1) SELECT TOP 3 Recordnum,Record_id FROM RandomMain ORDER BY NEWID()

INSERT Randomtwo(Recordnum2,Record_id2) SELECT TOP 3 Recordnum,Record_id FROM RandomMain ORDER BY NEWID()

and in table i have columns
Recordnum Record_id Recordnum1 Record_id1
4533 tfg <null> <null>
3555 hghg <null> <null>
4632 hgfw <null> <null>
6777 ghgh
555 gfhgf
4335 fghfgh

after execution of first statement records are inserted into Recordnum and Record_id
after Execution of second query records are insetred into Recordnum1 and Record_id1.but it leaves null space in between,i don't want that.I want records to be inserted inot column Record_id1 from first row itself.how can i do that.
please help

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-01-20 : 02:57:36

Please supply the table structures for Randomtwo and RandomMain - specifically regards defaults and nulls.

You are inserting columns into:

quote:
INSERT Randomtwo(Recordnum2,Record_id2)

yet you don't list those in your results.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 03:34:37

RandomMain contains

Recordnum Record_id
1 rtrt
2 wertt
3 ewre
4 ewt

Randomtwo Contains columns. Record_id2 and Recordnum2 can be null

Recordnum1 Record_id1 Recordnum2 Record_id2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 03:51:28

Can you do this ?

INSERT Randomtwo(Recordnum1,Record_id1,Recordnum2,Record_id2) 
SELECT TOP 3 Recordnum,Record_id,Recordnum,Record_id FROM RandomMain ORDER BY NEWID()

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 03:58:37

it works but same data is getting copied into the other two clumns as well i don't want that i.e Recordnum1 and Record_id1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 04:11:58

[code]INSERT Randomtwo(Recordnum1, Record_id1, Recordnum2, Record_id2)
SELECT TOP 3 r1.Recordnum, r1.Record_id , r2.Recordnum, r2.Record_id
FROM RandomMain r1 CROSS JOIN RandomMain r2
WHERE r1.Recordnum <> r2.Recordnum
AND r1.Record_id <> r2.Record_id
ORDER BY NEWID()[/code]

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 04:30:58

the query takes too much time it does not show any error but has not executed fully yet.

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 04:59:48

i get error

Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '(SYSTEM table id: -547780874)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 05:01:55

I was expecting that. It is using cross join. It might not be very efficient for large amount of records
This should be faster.

SELECT  TOP 3 r1.Recordnum, r1.Record_id , r2.Recordnum, r2.Record_id
FROM	
(
	SELECT 	TOP 6 Recordnum, Record_id
	FROM	RandomMain
	ORDER BY NEWID()
) as r1
CROSS JOIN 
(
	SELECT 	TOP 6 Recordnum, Record_id
	FROM	RandomMain
	ORDER BY NEWID()
) as r2
WHERE	r1.Recordnum	<> r2.Recordnum
AND	r1.Record_id	<> r2.Record_id
ORDER BY NEWID()

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 05:05:20

got it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 05:16:20

TOP 6 is the minimum number of records retrieve for each result set (r1 & r2) to ensure the TOP 3 of the cross join where r1 <> r2.

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-20 : 06:01:16

now if i want to insert different number of records in both Record_id1 and Record_id2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 08:17:49

you will and the scenario you have before and I tought you don't want this in the very first place ? ? ?

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources