June 3, 2010 at 8:52 am
I am hoping that someone can help.
I am using Visual Studio 2010 and have two tables in the Datasource window.
I can look at both tables and all their data.
I am trying to insert records that don't exist in one table into the other.
I created a query
INSERT INTO GPIV00101
(STPWH.dbo.GPIV00101.ITEMNMBR, STPWH.dbo.GPIV00101.ITEMDESC, STPWH.dbo.GPIV00101.NOTEINDX, STPWH.dbo.GPIV00101.ITMSHNAM, STPWH.dbo.GPIV00101.ITEMTYPE, STPWH.dbo.GPIV00101.ITMGEDSC, STPWH.dbo.GPIV00101.STNDCOST, STPWH.dbo.GPIV00101.CURRCOST, STPWH.dbo.GPIV00101.ITEMSHWT, STPWH.dbo.GPIV00101.DECPLQTY, STPWH.dbo.GPIV00101.DECPLCUR, STPWH.dbo.GPIV00101.ITMTSHID,
STPWH.dbo.GPIV00101.ITMCLSCD, STPWH.dbo.GPIV00101.ITMTRKOP, STPWH.dbo.GPIV00101.LOTTYPE, STPWH.dbo.GPIV00101.KPERHIST, STPWH.dbo.GPIV00101.KPTRXHST, STPWH.dbo.GPIV00101.KPCALHST, STPWH.dbo.GPIV00101.KPDSTHST, STPWH.dbo.GPIV00101.ALWBKORD, STPWH.dbo.GPIV00101.VCTNMTHD, STPWH.dbo.GPIV00101.UOMSCHDL, STPWH.dbo.GPIV00101.ALTITEM1, STPWH.dbo.GPIV00101.ALTITEM2,
STPWH.dbo.GPIV00101.USCATVLS_1, STPWH.dbo.GPIV00101.USCATVLS_2, STPWH.dbo.GPIV00101.USCATVLS_3, STPWH.dbo.GPIV00101.USCATVLS_4, STPWH.dbo.GPIV00101.USCATVLS_5, STPWH.dbo.GPIV00101.USCATVLS_6, STPWH.dbo.GPIV00101.MSTRCDTY, STPWH.dbo.GPIV00101.MODIFDT, STPWH.dbo.GPIV00101.CREATDDT, STPWH.dbo.GPIV00101.WRNTYDYS, STPWH.dbo.GPIV00101.PRCLEVEL,
STPWH.dbo.GPIV00101.LOCNCODE, STPWH.dbo.GPIV00101.PriceGroup, STPWH.dbo.GPIV00101.PRICMTHD, STPWH.dbo.GPIV00101.PRCHSUOM, STPWH.dbo.GPIV00101.SELNGUOM, STPWH.dbo.GPIV00101.ABCCODE, STPWH.dbo.GPIV00101.Revalue_Inventory, STPWH.dbo.GPIV00101.Tolerance_Percentage, STPWH.dbo.GPIV00101.ITMPLNNNGTYP, STPWH.dbo.GPIV00101.INACTIVE,
STPWH.dbo.GPIV00101.MINSHELF1, STPWH.dbo.GPIV00101.MINSHELF2, STPWH.dbo.GPIV00101.DEX_ROW_ID)
VALUES (A068.dbo.IV00101.ITEMNMBR, A068.dbo.IV00101.ITEMDESC, A068.dbo.IV00101.NOTEINDX, A068.dbo.IV00101.ITMSHNAM, A068.dbo.IV00101.ITEMTYPE, A068.dbo.IV00101.ITMGEDSC, A068.dbo.IV00101.STNDCOST, A068.dbo.IV00101.CURRCOST, A068.dbo.IV00101.ITEMSHWT, A068.dbo.IV00101.DECPLQTY, A068.dbo.IV00101.DECPLCUR, A068.dbo.IV00101.ITMTSHID,
A068.dbo.IV00101.ITMCLSCD, A068.dbo.IV00101.ITMTRKOP, A068.dbo.IV00101.LOTTYPE, A068.dbo.IV00101.KPERHIST, A068.dbo.IV00101.KPTRXHST, A068.dbo.IV00101.KPCALHST, A068.dbo.IV00101.KPDSTHST, A068.dbo.IV00101.ALWBKORD, A068.dbo.IV00101.VCTNMTHD, A068.dbo.IV00101.UOMSCHDL, A068.dbo.IV00101.ALTITEM1, A068.dbo.IV00101.ALTITEM2,
A068.dbo.IV00101.USCATVLS_1, A068.dbo.IV00101.USCATVLS_2, A068.dbo.IV00101.USCATVLS_3, A068.dbo.IV00101.USCATVLS_4, A068.dbo.IV00101.USCATVLS_5, A068.dbo.IV00101.USCATVLS_6, A068.dbo.IV00101.MSTRCDTY, A068.dbo.IV00101.MODIFDT, A068.dbo.IV00101.CREATDDT, A068.dbo.IV00101.WRNTYDYS, A068.dbo.IV00101.PRCLEVEL,
A068.dbo.IV00101.LOCNCODE, A068.dbo.IV00101.PriceGroup, A068.dbo.IV00101.PRICMTHD, A068.dbo.IV00101.PRCHSUOM, A068.dbo.IV00101.SELNGUOM, A068.dbo.IV00101.ABCCODE, A068.dbo.IV00101.Revalue_Inventory, A068.dbo.IV00101.Tolerance_Percentage, A068.dbo.IV00101.ITMPLNNNGTYP, A068.dbo.IV00101.INACTIVE,
A068.dbo.IV00101.MINSHELF1, A068.dbo.IV00101.MINSHELF2, A068.dbo.IV00101.DEX_ROW_ID)
WHERE (STPWH.dbo.GPIV00101.ITEMNMBR IS NULL)
When I try to run this code I get the following error message:
Msg 128, Level 15, State 1, Line 7
The name "A068.dbo.IV00101.ITEMNMBR" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
One of the articles I read here at SQLServerCentral said that you could write queries that would be able to use 2 databases as long as it was fully named.
I must be doing something wrong.
Any help anyone can give will be deeply appreciated.
Thanks
Frank:blink:
June 3, 2010 at 9:11 am
There are a couple of issues with this query,
First you are trying to do the insert using VALUES, this means you should be stating what the value is where it appears that you want the values from another table. In this case you dont need to state VALUES instead you should use a SELECT statement.
Also
WHERE (STPWH.dbo.GPIV00101.ITEMNMBR IS NULL)
will not work , if you want to select the values that do no exist on the destination table then you will need to do a left join on the destination table from your source to find the values that do not already exist
June 3, 2010 at 9:25 am
The syntax you're looking for is:
INSERT INTO STPWH.dbo.GPIV00101 (<column list, doesn't need to be qualified>)
SELECT (<column list, qualify with table alias ("i" in my example)>)
FROM A068.dbo.IV00101 i
LEFT JOIN STPWH.dbo.GPIV00101 g
ON g.ITEMNMBR = i.ITEMNMBR -- assuming that this is the proper match, change as appropriate
WHERE g.ITEMNMBR IS NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 9:30 am
Sample
CREATE TABLE MyNewTable
( id INT
, mychar CHAR(1))
GO
INSERT MyNewTable SELECT 1, 'A'
go
CREATE TABLE MyOldTable
( id INT
, mychar CHAR(1))
GO
INSERT MyOldTable SELECT 1, 'A'
INSERT MyOldTable SELECT 2, 'B'
INSERT MyOldTable SELECT 3, 'C'
INSERT MyOldTable SELECT 4, 'D'
go
SELECT * FROM mynewtable
SELECT * FROM myoldtable
INSERT MyNewTable
SELECT o.id, o.mychar
FROM MyOldTable o
LEFT OUTER JOIN MyNewTable n
ON o.id = n.id
WHERE n.mychar IS NULL
-- DROP TABLE MyNewTable
-- drop table MyOldTable
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply