Insert multiple records based on a select.

[CasperTFG]

Hey all,

I don't even know if this is possible, but I am hoping it is. What I need to do is, from an SQL insert multiple records, but I don't know how many?.

Essentially if I was doing this one at a time, I would do...

INSERT INTO T_CollectionMembers (UID, MemberUID) VALUES (?, 99999)

I can get what the ? mark is From...

SELECT UID FROM T_Collections

But what I want to do is build an SQL where it will insert 1 Collecion Member Record, for each number of collections.

Any thoughts?

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[apexbs]

Your question is not very clear to me but you can try that

INSERT INTO T_CollectionMembers (UID, MemberUID)
SELECT UID , 99999 FROM T_Collections

I hope it would work.

 

[dbomrrsm]

Dont know if you have access to the table but you can set the field to be incremental - the easiest way is to do it through EM. Then you can increment it each time a new record is put in to the T_CollectionMembers table where the insert will just become:

insert into INSERT INTO T_CollectionMembers (UID)
Select SELECT UID FROM T_Collections

then SQL Server will just increment the MemberUID field each time a new record is inserted. You can set the increment to be what you like - by one or by 10 etc.

[blue]DBomrrsm[/blue]

 

[CasperTFG]

I was thinking this would work, but it doesn't...

INSERT INTO Table1 (UID, MemberUID) VALUES ((SELECT UID FROM T_ObjectGroups), 99999)

Okay try to make it a little more clear...

I have 3 Tables...
[tt]
[T_Collections]
[UID] [Name]
10101 'GroupA'
10102 'GroupB'
10103 'GroupC'
10104 'GroupD'

[T_CollectionMembers]
[UID] [MemberUID]
10101 28436
10101 28437
10101 28438

[T_CollectionMembers]
[UID] [Name]
28436 'Item 1'
28437 'Item 2'
28438 'Item 3'
[/tt]
So I know that Item 1, belogs to Group A, because the Member Record links them... Now what I want to to is, via SQL add a New Item, with the ID of 99999, and have it belong to all the groups. So my new tables would look like...
[tt]
[T_Collections]
[UID] [Name]
10101 'GroupA'
10102 'GroupB'
10103 'GroupC'
10104 'GroupD'

[T_CollectionMembers]
[UID] [MemberUID]
10101 28436
10101 28437
10101 28438
10101 99999
10102 99999
10103 99999
10104 99999

[T_CollectionMembers]
[UID] [Name]
28436 'Item 1'
28437 'Item 2'
28438 'Item 3'
99999 'Global Item'
[/tt]





Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

No... I know what the Numbers should be... But I need to Dynamically insert... The 99999 is constant, but I don't or won't know what my customers have created group wise or what the ID numbers on those groups are.



Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

Interesting...

INSERT INTO CollectionMembers (UID)
(SELECT UID FROM T_Collections)

This works, for inserting in all the UID's from T_Collections, however, I can't add the second default value of 99999

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

apexbs,

Your way work... LOL it looked too easy I didn't think it would...

Thanks.


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".