Insert into... select - 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 2000 Forums

Transact-SQL (2000)

Insert into... select

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-05-22 : 11:07:27

Is it possible to have two select statements with the Insert Into?

Like this:

Insert into #temp (Field1, Field2)
Select fld5 from....,
Select fld6 from....

I tried the above syntax and it doesn't work that way or any other way close to that. Can this even be done? what are the alternatives?

I need to have a table with single record that has fields populated with values from different select statements.

Thanks.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-22 : 11:17:16


Insert into #temp (Field1, Field2)
SELECT
  (Select fld5 from....),
  (Select fld6 from....)

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-05-22 : 11:26:58

Yes, I tried that but I get a message saying that there is incorrect syntax near ',' on line (Select fld5 from....),

1fred
Posting Yak Master

158 Posts

Posted - 2003-05-22 : 11:39:36

insert into table
select * from table1
union all
select * from table2

The all is important if you want all the values, if you put "all" you can have duplicate, if you don't you'll only have distinct values

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-05-22 : 12:12:24

When I do this I get an error while running saying # of suppliwd values doesn't match table definition.

This statement will add both values from two select statements into new table under one column, but I need it to be in two column (fileds)

Table:
Field1 Field2
select statement1 select statemnet2

mtomeo
Starting Member

30 Posts

Posted - 2003-05-22 : 12:35:24

I think I understand what you are saying, but how do the columns relate to each other?

Stop me if I'm off the mark, but you have:

Table1
f1
--
x
y
z

Table2
f2
--
1
2
3

and You want:

NewTable
f1 f2
-- --
x    1
y    2
z    3

But how do these columns (or better yet Tables) relate? What tells me that x goes with 1?

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-05-22 : 12:56:59

Yes, you are getting my idea.

The two tables do not relate to each other. Each table will only have one value. So the resulting table will have one record.
Table1
f1
--
x

Table2
f2
--
1

and You want:

NewTable
f1 f2
-- --
x 1

mtomeo
Starting Member

30 Posts

Posted - 2003-05-22 : 13:31:48

I'm sure someone has something better, but if there is truely only one row in each of those tables, I guess you could do this:


declare @field1 varchar(10), @field2 varchar(10)
set @field1 = (select field5 from table1)
set @field2 = (select field6 from table2)
insert into #temp (field1, field2)
values (@field1, @field2)

(I refuse to post a 2 table Select w/out a Where, which would be the easiest solution,
but wrong on so many levels)

Edited by - mtomeo on 05/22/2003 13:33:18

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-22 : 15:37:08

quote:

Yes, I tried that but I get a message saying that there is incorrect syntax near ',' on line (Select fld5 from....),

No you don't.


CREATE TABLE #t1 (f1 char(1) NOT NULL)
CREATE TABLE #t2 (f2 int NOT NULL)

INSERT INTO #t1 SELECT 'x'
INSERT INTO #t2 SELECT 1

CREATE TABLE #temp (Field1 char(1) NOT NULL, Field2 int NOT NULL)

INSERT INTO #temp (Field1, Field2)
SELECT
  (SELECT f1 FROM #t1),
  (SELECT f2 FROM #t2)

SELECT * FROM #temp

Edited by - Arnold Fribble on 05/22/2003 15:40:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources