Inserting Multiple Records - 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)

Inserting Multiple Records

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2007-02-22 : 11:36:23

My situation: I am querying TableA and getting three UserIDs (in my test system) and I want to create three new records in TableB that include the UserIDs and some set values (see below) but the SQL I have now is only inserting one new record. What am I doing wrong? I might have 400 records to insert in the live system, i mentioned the three because thats what i have in the test system.

If you know how I can insert all records returned from the select (along with the other hardcoded values below) please let me know what the syntax is. thanks.

<CODE>
DECLARE @UserID int

SELECT @ UserID = UserID FROM TableA
WHERE PartID='X105' AND Active=1

BEGIN

INSERT INTO TableB
([UserID]
,[sessionID]
,[UserType]
,[Price]
,[MemberType])
VALUES
(@UserID
,140
,1
,0.0
,1)
END
</CODE>

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 12:48:21

[code]INSERT INTO TableB
([UserID]
,[sessionID]
,[UserType]
,[Price]
,[MemberType])
Select
UserID,
... (type desired column names here)
FROM TableA
WHERE PartID='X105' AND Active=1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

MyronCope
Starting Member

46 Posts

Posted - 2007-02-22 : 15:05:12

Thanks, this is very close to what I need but the difference is that I have certain numeric values that are hard coded into my script. In other words, I only need to select the UserID from a Table, the other values that I want to insert are known constant numbers (that are listed above). Is it possible to use both the select statement and hard coded values at the same time? please show syntax if it can be done, thanks.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2007-02-22 : 15:24:14

Ummm.. try this:

INSERT INTO TableB
([UserID]
,[sessionID]
,[UserType]
,[Price]
,[MemberType])
Select
UserID,
140 AS [sessionID],
1 AS [UserType],
0.0 AS [Price],
1 AS [MemberType]
FROM TableA
WHERE PartID='X105' AND Active=1

quote:
Originally posted by MyronCope

Thanks, this is very close to what I need but the difference is that I have certain numeric values that are hard coded into my script. In other words, I only need to select the UserID from a Table, the other values that I want to insert are known constant numbers (that are listed above). Is it possible to use both the select statement and hard coded values at the same time? please show syntax if it can be done, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources