Urgent insert statement help....

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

SQL Server Development (2000)

Urgent insert statement help....

Author

Topic

Bad ASh
Starting Member

8 Posts

Posted - 2006-12-30 : 03:48:03

Hi all, having drams with this insert query.

declare @worksheetid integer
set @worksheetid = (select max([id])from worksheet where style = 260 and status = 2)

DECLARE @today datetime
SET @today = CONVERT(VARCHAR(10), getdate(), 101)

insert into worksheet_itemupdate (worksheetid,itemid)
Values (@worksheetid,(SELECT [id] from item where CONVERT(VARCHAR(10), datecreated, 101) = @today))

result:

An error occurred while executing query:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

any ideas?
thanx, from the noob.

"Evil will always triumph because good.....is dumb"

Kristen
Test

22859 Posts

Posted - 2006-12-30 : 05:35:37

insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid,(SELECT [id] from item where CONVERT(VARCHAR(10), datecreated, 101) = @today)

You don't actually need to do the preliminary variable calculations, you can do it all in one INSERT ... SELECT statement - if you want to!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 00:00:18

1 Make sure that your subquery always return only one value
2 Other approach
insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid,[id] from item where CONVERT(VARCHAR(10), datecreated, 101) = @today

Actully your where clause should be

where datecreated>=Dateadd(day,Datediff(day,@today,0),0) and
datecreated<Dateadd(day,Datediff(day,@today,0),1)

provided your variable assignment is

DECLARE @today datetime
SET @today = getdate()

Madhivanan

Failing to plan is Planning to fail

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-04 : 01:36:38

Thanx for the input, I tried the
insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid,(SELECT [id] from item where CONVERT(VARCHAR(10), datecreated, 101) = @today)
and it works great....But only if there is 1 Value, unfortunately i need to insert multiple id records.

any tips...

"Evil will always triumph because good.....is dumb"

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 02:23:25

Do you mean:

1) You want to only insert one row, but there are multiple matching ID records? (If so WHICH ID do you want to match - e.g. MAX(ID) ?)
2) You want to insert multiple rows, for all matching IDs

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 04:21:14

quote:
Originally posted by Bad ASh

Thanx for the input, I tried the
insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid,(SELECT [id] from item where CONVERT(VARCHAR(10), datecreated, 101) = @today)
and it works great....But only if there is 1 Value, unfortunately i need to insert multiple id records.

any tips...

"Evil will always triumph because good.....is dumb"

Did you read my point 2?

Madhivanan

Failing to plan is Planning to fail

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-04 : 16:56:53

I will be inserting multiple rows with different (item)id's
an example of the data to go is so.

id worksheetid itemid

1 85 234
2 85 235
3 85 239

"Evil will always triumph because good.....is dumb"

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 01:04:48

This then probably:


insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid, [id] 
from item 
where CONVERT(VARCHAR(10), datecreated, 101) = @today

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-05 : 08:09:10

Kristen, do you mean my second point is correct?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 10:25:41

Nope, based on the fact that Bad ASh didn't think so, your formatting was probably wrong (I expect you format your source code in the Client App?!) - despite the subject saying "Urgent" ...

It took Bad ASh 5 days to come back after the first question, and 12 hours after the second - maybe its getting more urgent??

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-06 : 00:16:15

Thankyou Kristen, the script worked perfectly.
And to all of you who posted a reply I thankyou too.

The app i'm using is Microsoft Retail Management System, It has an area that allows direct sql statement to be run for data manipulation.

I've been working with it for a while and have been forced to learn SQL the hardway.

Thankyou all again.
Cheers and a Happy New Year from me in OZ.

"Evil will always triumph because good.....is dumb"

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-06 : 03:20:34

ok.....i'm nearly there with the script i need.

so far i have all this working.

insert into worksheet (style,effectivedate,status,notes,title,fromdate)
values (260,getdate(),2,'IGA Host File Update of New Items','Download New IGA Items',getdate())

declare @worksheetid integer
set @worksheetid = (select max([id])from worksheet where style = 260 and status = 2)

DECLARE @today datetime
SET @today = CONVERT(VARCHAR(10), getdate(), 101)

insert into worksheet_itemupdate (worksheetid,itemid)
SELECT @worksheetid, [id]
from item
where CONVERT(VARCHAR(10), datecreated, 101) = @today

insert into worksheethistory (worksheetid,status,storeid)
select @worksheetid,'0',[id] from store where [id] <>0

insert into worksheetstore (worksheetid,status,storeid)
select @worksheetid,'0',[id] from store where [id] <>0

there is one more table i need to insert into...
i tried this script but it fails on the last entry

insert into itemdynamic (itemid,taxid,reorderpoint,restocklevel,snapshotprice,snapshotcost,storeid)
select itemid,taxid,reorderpoint,restocklevel,price,cost from item where CONVERT(VARCHAR(10), datecreated, 101) = @today and [id] from store where [id]<>0

any tips???

thanx in advance.

"Evil will always triumph because good.....is dumb"

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 12:47:10

Not sure what you are trying to achieve, might help if you describe it.

You are getting all the columns you need from ITEM table except the "STOREID" column value.

Looks like you are trying to get that from the STORE table.

In that case there would need to be some correlation between the ITEM and the STORE tables

Is that right?

Kristen

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-06 : 17:51:23

Thats right, I need the id from the store table as well as the selected fields from the item table.

"Evil will always triumph because good.....is dumb"

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 02:41:16

"Thats right, I need the id from the store table as well as the selected fields from the item table"

Then you need to JOIN those two tables in some column(s) that contain associated data, and the columns from both tables will then be available to your SELECT statement:


SELECT A.Col1, A.Col2, B.Col998, B.Col999
FROM Table_A AS A
     JOIN Table_B AS B
         ON B.MyID = A.MyID

Kristen

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-07 : 21:39:35

unfortunately there is no common or direct link between the store and item table.

i can manually add the id's (1,2,3 etc) but if more stores are added i will need to keep modifying it as they go.

"Evil will always triumph because good.....is dumb"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 23:47:35

quote:
Originally posted by Bad ASh

unfortunately there is no common or direct link between the store and item table.

Who's system is it? Your's or a third-party vendor?
There is no link between the two tables and yet you want to calculate them together?

Peter Larsson
Helsingborg, Sweden

Bad ASh
Starting Member

8 Posts

Posted - 2007-01-08 : 00:06:04

The system is a Microsoft product, we are looking at an add on that alows importing of a suppliers product list into the central headquarters database. but that is as far as it goes. we need to script the tasks to get that information from HQ to the stores.

the HQ Database has 2 item tables.
1. item (is the master product list)
2. itemdynamic (contains a copy for each store of the master item) eg. 3 entries in the item table, for 3 stores = 9 entries in the itemdynamic table.

The item table does not have a storeid but the itemdynamic does which links back to the store table. (this is to alow the stores to have varying costs and prices)

when the import is performed it only creates an entry in the item table and not the itemdynamic table. (which is what i'm trying to do)

Hope i havent confused you even more...
Cheers.

"Evil will always triumph because good.....is dumb"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources