INSERT command using values from another query

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 command using values from another query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-10 : 12:27:35

Hi,

Could someone advise me the correct syntax to write this

INSERT INTO tblHomePageSlider (vehicleref, capid, make, model, derivative, category)
VALUES (SELECT vehicleref, capid, make, model, derivative, locatcode
FROM dbPubMatrix..tblNewMatrixStd)

Thank you

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-01-10 : 12:30:21

[code]INSERT INTO tblHomePageSlider (vehicleref, capid, make, model, derivative, category)
~~VALUES (~~SELECT vehicleref, capid, make, model, derivative, locatcode
FROM dbPubMatrix..tblNewMatrixStd)[/code]

Mark

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-10 : 12:39:38

Thank you! Works great.

Trying to do an update using the same logic


UPDATE tblHomePageSlider
SET co2 = (SELECT TECH_Value_String FROM PUB_CAR.dbo.NVDTechnical
WHERE (TECH_TechCode = 67) AND (TECH_Id = tblHomePageSlider.capID))

I get this error

Subquery returned more than one value. Am I doing it correctly?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 12:43:36

You need to ensure you return only 1 value from subquery. Run it individually and see if it gives more. Then based on your requirement take top1,...

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-01-10 : 16:48:41

how about:


UPDATE tHPS
SET co2 = 
  (
  SELECT TOP 1
    TECH_Value_String
  FROM
    PUB_CAR.dbo.NVDTechnical
  WHERE
    TECH_TechCode = 67
    AND
    TECH_Id = tblHomePageSlider.capID
  )
FROM
  tblHomePageSlider tHPS

___________________________
Geek At Large

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources