SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using table variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DueyDooDah
Starting Member

USA
3 Posts

Posted - 08/16/2011 :  14:48:37  Show Profile  Reply with Quote
I am attempting to access a table variable in the code below. in the last INSERT statement, I recive an error indicating the parameter(s) need to be scalar. Can someone point me in the right direction to accomplish what I am trying to to (access the columns of the loaded table variable and insert the data into another table [Computer])

Use [PC Inventory]
go

IF OBJECT_ID ('UpdateTables','P') IS NOT NULL
DROP PROCEDURE UpdateTables
GO

CREATE PROCEDURE UpdateTables
AS
BEGIN

DECLARE @tbl TABLE
(
[PC Name] nvarchar (255),
FirstName nvarchar (255),
LastName nvarchar (255),
[Office Code] nvarchar (255),
Location nvarchar (255),
[Use] nvarchar (255),
OS nvarchar (255),
Chip nvarchar (255),
[RAM (MB)] nvarchar (255),
[Hard drive size] nvarchar (255),
[System] nvarchar (255),
[S/N or Svc Tag] nvarchar (255),
[Model number] nvarchar (255),
[Type (L or D)] nvarchar (255),
[Other Cpmponents] nvarchar (255),
Device nvarchar (255),
[Dock S/N] nvarchar (255),
Monitor nvarchar (255),
[Monitor S/N] nvarchar (255),
Warranty nvarchar (255),
Acquired DATETIME,
[Warranty Expires] DATETIME,
Retired DATETIME,
Vendor nvarchar (255),
[Budget Center] nvarchar (255),
[Total Price] MONEY,
Notes nvarchar (255),
[XP OS Product Key XP] nvarchar (255),
[License info] nvarchar (255),
[Donated To] nvarchar (255),
[Date donated] DATETIME,
[Item Type] nvarchar (255),
[Path] nvarchar (255)
)

INSERT INTO @tbl SELECT * FROM temptable
WHERE [S/N or Svc Tag] NOT IN
(SELECT SerialNumber FROM Computer)

INSERT INTO Computer ( ComputerName, SerialNumber, PurchaseDate )
VALUES
( @tbl.[PC Name], @tbl.[S/N or Svc Tag], @tbl.[Acquired] )

END

tkizer
Almighty SQL Goddess

USA
36985 Posts

Posted - 08/16/2011 :  14:53:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you moving the data twice?

Why not skip the table variable?:

INSERT INTO Computer ( ComputerName, SerialNumber, PurchaseDate )
SELECT * FROM temptable
WHERE [S/N or Svc Tag] NOT IN
(SELECT SerialNumber FROM Computer)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

DueyDooDah
Starting Member

USA
3 Posts

Posted - 08/16/2011 :  15:03:36  Show Profile  Reply with Quote
Yes, I should have explained a bit on that. My intention is to do just as you say. But, as I am new to this I wanted to walk through how data moves with the debugger and watch how it all works. Also, because I have many related tables to update using this information, I wanted to do a step-by=-step examination of each column transfer to ensure I don't miss anything, especially when I must update the relational data.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36985 Posts

Posted - 08/16/2011 :  15:06:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Inserting into a table variable is really no different than a normal table:

INSERT INTO Computer ( ComputerName, SerialNumber, PurchaseDate )
select [PC Name], [S/N or Svc Tag], [Acquired]
from @tbl

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 08/16/2011 15:07:10
Go to Top of Page

DueyDooDah
Starting Member

USA
3 Posts

Posted - 08/16/2011 :  15:37:17  Show Profile  Reply with Quote
Excellent! Thank you very much. I guess I still need more study on the nuances of the SELECT statement.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36985 Posts

Posted - 08/16/2011 :  15:45:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000