Generally, you can't use a variable in this way, correct?

INSERT table (column1, column2) VALUES (@variable, '100')

Do I need to be learning about stored procedures to make something like
this work?  Do I need to restructure the insert statement?

Thanks

-tom

That should work, otherwise use the SELCT syntax:

INSERT table (column1, column2)
SELECT @variable, '100'


HTH, Jens Suessmeyer.

Tom,

Yes you can use variables in a Table Insert.

create table dbo.Test
(Col1 varchar(10),
Col2 varchar(10)
)
Go

declare @Test varchar(10)
set @Test = 'Test'

Insert into dbo.Test (col1, col2)
Values (@Test, 'Test2')
Go

select * From dbo.Test
Go

Drop dbo.Test
Go


HTH

Barry

Sorry I meant ....

Drop Table dbo.Test
Go

!

Barry

tom wrote:
I don't see anything wrong with this, given that @variable is declared and
defined.

I don't understand the question. It should work as is. Run this script to
see:

create table #temp (
column1    int,
column2    varchar(3))

declare @variable int
set @variable=200
INSERT INTO #temp (column1, column2)
VALUES (@variable, '100')

SELECT * FROM #temp
drop table #temp

No, what is making you think you have to?

Bob Barrows

Tom,

The INSERT would work but a proc would be better.

Examples to work with:

CREATE TABLE TESTTABLE101
(COLUMN1 VARCHAR(10) NOT NULL,
COLUMN2 VARCHAR(10) NOT NULL)
GO

DECLARE @VARIABLE VARCHAR(10)
SET @VARIABLE = 'WHATEVER'
INSERT TESTTABLE101
VALUES(@VARIABLE, '100')
GO

SELECT * FROM TESTTABLE101
GO

CREATE PROC USP_TESTPROC
@VARIABLE VARCHAR(10)
AS
INSERT TESTTABLE101
VALUES(@VARIABLE, '100')
GO

EXEC USP_TESTPROC 'WHATEVER2'
GO

SELECT * FROM TESTTABLE101
GO

--DROP PROC USP_TESTPROC
--DROP TABLE TESTTABLE101

HTH

Jerry
Show quoteHide quote

Is it possible that adding double quotes and single quotes changed the
outcome?  I adding quotes thus: " ' {first double quotes and then
single quotes} and it worked.    Specifically, this works: VALUES
('100', '" @variable "'), though you can't tell the order in that text.
I'm using QA.

Thank you everyone for your incredibly quick responses.

-tom

tom wrote:
Did you run my script? Why are you messing around with double quotes? Are
you trying to create a dynamic sql statement? If so, see
http://www.sommarskog.se/dynamic_sql.html

My suggestion would be to not use dynamic sql.

You are prolonging this process by failing to give us enough information.
Show us the code needed to cause the error you are getting. Oh yeah! And
tell us what error you are getting ...

Here it is . ..


set nocount on
declare fix_cursor cursor
for select ID from mock

declare @thiskey char(10)
open fix_cursor
fetch next from fix_cursor into @thiskey

while @@FETCH_STATUS = 0
begin
    insert into tasks_full (taskid, taskactkey)
    values ('" @thiskey "', (select max(actkey) from actions_full where
actid = '"@thiskey"'))
    fetch next from fix_cursor into @thiskey
end

close fix_cursor
deallocate fix_cursor

For this statement I get the following error:
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '" @thiskey "'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ')'.

-tom

This worked:

set nocount on

declare fix_cursor cursor
for select ID from mock

declare @thiskey char(10)

open fix_cursor
fetch next from fix_cursor into @thiskey

while @@FETCH_STATUS = 0
begin
    print @thiskey
    insert into tasks_full (taskid, taskactkey)
    select @thiskey, (select max(actkey) from actions_full where actid =
@thiskey)

    fetch next from fix_cursor into @thiskey

end

close fix_cursor
deallocate fix_cursor

You guys all helped a ton!  Give me a paypal account, and I'll pay . .
..

-tom

tom wrote:
Show quoteHide quoteDon't use a slow inefficient cursor when a simple insert statement will
perform your task. Try this:

insert into tasks_full (taskid, taskactkey)
Select m.ID, MaxKey FROM mock m inner join (
Select actid, max(actkey) MaxKey from actions_full
group by actid) q on m.ID = q.actid

Actually, I'm not sure you even need to use that mock table. Why not just
create a view:

Create View mock AS
Select m.ID, MaxKey FROM mock m inner join (
Select actid, max(actkey) MaxKey from actions_full
group by actid) q on m.ID = q.actid

Not necessary. I do not do this for monetary reward. But thanks anyway and
you're welcome.

Bob Barrows

I like that a lot more.  Very clean, using simple SQL statements.  I
need to play with it to see how it works, but thanks for posting, Bob.

-tom

One thing that I am not sure how to do is create an INSERT statement
that mixes the SELECT statement with some constants.
For instance,
insert into tasks_full (taskid, taskcomment, taskdate)
select m.ID, 'Comment for Tasks', 'Nov 1, 2005'

What is the proper syntax for that?  Do I need parens?

Thanks again.

-tom

tom wrote:
First of all a SELECT statement does not require a FROM clause to work. Try
running this in QA:

SELECT 1, 'Comment for Tasks', '20051101'

You need a FROM clause if your select list includes the name of a column
(m.ID).

Here's an illustration which you can run in QA:

CREATE TABLE #temp (
taskid    int,
taskcomment    varchar(50),
taskdate datetime)
go
CREATE TABLE #temp2 (
ID    int)
go

INSERT INTO #temp2
select 3 union
select 4 union
select 5 union
select 6

--two techniques for inserting constant values:

INSERT INTO #temp(taskid, taskcomment, taskdate)
VALUES(1, 'Comment for Tasks', '20051101')

INSERT INTO #temp(taskid, taskcomment, taskdate)
SELECT 2, 'Comment for Tasks', '20051101'
select * from #temp

--inserting data from another table combined with constant values
INSERT INTO #temp(taskid, taskcomment, taskdate)
SELECT ID, 'Comment for Tasks', '20051101'
FROM #temp2
select * from #temp

HTH,
Bob Barrows

Bob, thank you very much for your help.

-tom

Sure, why not.

All Inserts/Updates/Deletions in databases where I work are done through
stored procedures.
There are exceptions but usually they are one-time jobs by the database DBA,
tested first on a database copy before they are committed to the production
database.
Even Selects go through SPs. Nobody has direct access to tables and queries
except the database DBA and programmers.
I bet quite a few people in the newsgroup here would agree on this method.

So, to answer your second question, YES... learn about stored procedures.

Show quoteHide quote

Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?
Grouping,Paging Logic
sORTING ACROSS MULTIPLE COLUMN
Strange result