SQL SELECT INTO ? - 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

SQL SELECT INTO ?

Author

Topic

ricc
Starting Member

16 Posts

Posted - 2007-04-17 : 15:47:27

I have two tables:

1. Properties

PropertyID int
PropertyName varchar(20)

2. Matches

UserID int
PropertyID int

I have the following SQL query that will seach for property matches from the Properties table and insert the matching propertyID into the Matches table:

@SearchParam varchar(20),
@UserID int --not used yet...but want to insert into Matches too
As

SELECT PropertyID INTO Matches FROM Properties WHERE SearchParam = @SearchParam
GO

What I want to do is also insert the UserID with the PropertyID into the Matches table. I have tried :

SELECT PropertyID, @UserID INTO Matches FROM Properties WHERE SearchParam = @SearchParam

But this does not work? Any ideas would be great!

Thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-17 : 15:59:43

Try putting a column alias for your User ID:

SELECT PropertyID, @UserID AS UserID INTO Matches FROM Properties WHERE SearchParam = @SearchParam

SQL Server Helper
http://www.sql-server-helper.com

ricc
Starting Member

16 Posts

Posted - 2007-04-17 : 16:18:02

Thanks For the reply...

I actually got confused, I ment to use an INSERT INTO not SELECT INTO...

So When i do:

INSERT INTO Matches (PropertyID, UserID) SELECT PropertyID, @UserID FROM Properties WHERE SearchParam = @SearchParam

My new question is, is there any way to only insert the matches is they DO NOT alredy exist in the Matches table?

Thanks again

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-17 : 16:32:58

Something like:
INSERT INTO Matches (PropertyID, UserID) SELECT PropertyID, @UserID FROM Properties WHERE SearchParam = @SearchParam and PropertyID not in (select PropertyID from Matches)

ricc
Starting Member

16 Posts

Posted - 2007-04-17 : 17:34:56

Prefect thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources