setting variables using a list

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 2000 Forums

Transact-SQL (2000)

setting variables using a list

Author

Topic

einzelgaenger
Starting Member

2 Posts

Posted - 2004-10-06 : 02:26:18

I haven't touch SQL in so long I can't remember much. Here is what I was trying to do. Create a simple two column table using input from other tables. I am trying to have it insert multiple values using variables. I am trying to take the variables from a column in another table. The problem is that the subquery returns multiple values (which I wanted), but it is a syntax error. I need to find a way to have it insert a new line into the new table for each record in the import table. Hope this makes sense. Here is the transact-sql:

IF OBJECT_ID ('dbo.SUS_Numbers') IS NOT NULL
DROP TABLE dbo.SUS_Numbers
GO
CREATE TABLE dbo.SUS_Numbers
(
Number VARCHAR(50)NULL ,
Total VARCHAR(50) NULL
)
GO
--Insert data
DECLARE @qnum1 varchar(50)
SET @qnum1 = (select col1 from dbo.import)
DECLARE @qnum2 varchar(50)
SET @qnum2 = (select col2 from dbo.import)
DECLARE @tot varchar(50)
SET @tot = (Select count(*) FROM dbo.sus_data where (ItemInfo LIKE @qnum2) and (Activity = 'Download') AND (Status = 'Succeeded'))

INSERT INTO dbo.SUS_Numbers (Number, Total)
VALUES (@qnum1, @tot)
GO
--View data
SELECT * from dbo.SUS_Numbers
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-06 : 02:56:00

insert into dbo.sus_numbers
select col1,count(*) from dbo.sus_data
where iteminfo in (select col2 from dbo.import) and
(Activity = 'Download') AND (Status = 'Succeeded')

will this do?

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-06 : 03:15:31

insert into dbo.sus_numbers
select col1,count(*) from dbo.sus_data, dbo.import
where iteminfo = col2
and Activity = 'Download'
and Status = 'Succeeded'
group by col1

Hemanth Gorijala
BI Architect / DBA...

einzelgaenger
Starting Member

2 Posts

Posted - 2004-10-06 : 03:22:33

It looks like it works. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources