INSERT question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT question

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 13:34:39

How can I accomplish to populate a 2-column table with two different queries ?


CREATE TABLE #forms 
	(old_id smallint,new_id smallint)

INSERT INTO #forms(old_id,new_id)
	(SELECT FormNameID 
	 FROM Forms),1

Doesn't work.
Any ideas ?
Thanks,

robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-24 : 13:37:33

INSERT INTO #forms(old_id,new_id)
SELECT FormNameID, 1
FROM Forms

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 13:45:07

I'm sorry I should have been more clear with my question.


CREATE TABLE #forms 
	(old_id smallint,new_id smallint)

INSERT INTO #forms(old_id,new_id)
	(SELECT FormNameID 
	 FROM Forms),(select SomeOtherID from OtherFormtable)

Thanks,

robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-24 : 13:46:57

Please show us sample data from both of your tables and also show us what data should get inserted into #forms.

It's possible that this can be done with a cross join, but we really need to see an example to determine what the query should look like.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 13:54:40

The data is unrelated.

Forms
--------------------------
functionid || formnameid
10----------20
20----------35

SomeOtherTable
--------------------------
someid || someotherid
4---------420
5---------500

robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-24 : 14:03:44

Please show us what rows should be inserted into #forms.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 14:13:20

from table Forms, formnameid rows ( 20 and 35)
from table SomeOtherTable, someotherid rows ( 420 and 500 )

robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-24 : 14:24:23

Programmatically, how can we determine which value from Forms gets matched up with which value from SomeOtherTable?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 14:28:04

That's a good point. I have no idea how to do this, yet. I might try to do something with cursors.

Thanks,

robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-24 : 14:33:23

If you want the cartesian product between the two tables, then you can use a cross join.

--INSERT INTO #forms(old_id,new_id)
SELECT FormNameID, someotherid
FROM Forms
CROSS JOIN SomeOtherTable

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 14:55:22

That's a solution.

Thanks,

robert

eurob
Posting Yak Master

100 Posts

Posted - 2007-05-24 : 16:01:07

Actually it worked better with two cursors because the Cartesian product would give me to many rows.

Thanks,

robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources