February 27, 2012 at 2:26 pm
I have to update a table based on data from another table. Both ways I tried are not working for me.
update VectorReplica.dbo.PARCELS set PARCEL_ID = new_pid from Ranieranneaxparcels
where VectorReplica.dbo.PARCELS.PARCEL_ID = Ranieranneaxparcels.NEW_PID
update VectorReplica.dbo.PARCELS set PARCEL_ID = b.new_pid from Ranieranneaxparcels
inner join Ranieranneaxparcels as b on
([VectorReplica].[dbo].[PARCELS].[PARCEL_ID] = b.[NEW_PID])
The first one when I parse it it says no errors but when I rn it say no rows updated.
The seconds parses fine too but I get this error message:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "VectorReplica.dbo.PARCELS.PARCEL_ID" could not be bound.
Not sure what I am doing wrong.
Thank you in advance for your help.
Quinn
February 27, 2012 at 2:59 pm
Quinn-793124 (2/27/2012)
I have to update a table based on data from another table. Both ways I tried are not working for me.update VectorReplica.dbo.PARCELS set PARCEL_ID = new_pid from Ranieranneaxparcels
where VectorReplica.dbo.PARCELS.PARCEL_ID = Ranieranneaxparcels.NEW_PID
update VectorReplica.dbo.PARCELS set PARCEL_ID = b.new_pid from Ranieranneaxparcels
inner join Ranieranneaxparcels as b on
([VectorReplica].[dbo].[PARCELS].[PARCEL_ID] = b.[NEW_PID])
The first one when I parse it it says no errors but when I rn it say no rows updated.
The seconds parses fine too but I get this error message:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "VectorReplica.dbo.PARCELS.PARCEL_ID" could not be bound.
Not sure what I am doing wrong.
Thank you in advance for your help.
Quinn
That table isn't in your query. You are in effect using a select statement as the basis for your update.
You should be to change your update to a select and it will work if you have it coded correctly.
--update VectorReplica.dbo.PARCELS set PARCEL_ID = b.new_pid
select * from
from Ranieranneaxparcels
inner join Ranieranneaxparcels as b on
([VectorReplica].[dbo].[PARCELS].[PARCEL_ID] = b.[NEW_PID])
See where the error is now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2012 at 9:32 pm
Hope this should work. Please try this...
update VectorReplica.dbo.PARCELS set PARCEL_ID = b.new_pid from Ranieranneaxparcels AS A
inner join VectorReplica.dbo.PARCELS as B on
(B.[PARCEL_ID] = b.[NEW_PID])
-Ravi.
Regards,
Ravi.
March 8, 2012 at 8:34 am
Sorry it took so long to respond. Got side tracked.
I tried this from one of the responces.
update VectorReplica.dbo.PARCELS set PARCEL_ID = a.new_pid from Ranieranneaxparcels AS A
inner join VectorReplica.dbo.PARCELS as B on
(B.[PARCEL_ID] = a.[NEW_PID])
No rows updated.
So I tried this.
select * from Ranieranneaxparcels as c
inner join VectorReplica.dbo.PARCELS as b on
(b.PARCEL_ID = c.[NEW_PID])
Of course no rows again.
I thought maybe that the join was wrong so I tried outer joins but they didn't work.
I can get the right rows I need to update with this. 392 rows out of 412.
select OLD_PARCELNBR, NEW_PID from Ranieranneaxparcels c where
c.OLD_PARCELNBR in
(select a.parcel_id from VectorReplica.dbo.parcels a
where c.OLD_PARCELNBR = a.PARCEL_ID)
Is there a way to incorporate this select into a update command?
Thanks
Quinn
March 8, 2012 at 8:39 am
Can you post ddl and some sample data along with desired results based on the sample data? Take a look at the first link my signature for how to post this data in a format to elicit the best responses.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 9:01 am
USE [VectorReplica]
GO
/****** Object: Table [dbo].[PARCELS] Script Date: 03/08/2012 09:47:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PARCELS](
[OBJECTID] [int] NOT NULL,
[TOWN] [numeric](38, 8) NULL,
[RANG] [numeric](38, 8) NULL,
[SECT] [numeric](38, 8) NULL,
[PARCEL_ID] [nvarchar](12) NULL,
[PIDCODE] [nvarchar](12) NULL,
[PIDCODE2] [nvarchar](30) NULL,
[Water] [smallint] NULL,
[Hosp] [smallint] NULL,
[Fire] [smallint] NULL,
[Amb] [smallint] NULL,
[ComDis1] [smallint] NULL,
[SchD1] [nvarchar](4) NULL,
[GISAcres] [numeric](38, 8) NULL,
[TAXYEAR] [smallint] NULL,
[ParclNbr] [nvarchar](15) NULL,
[MultPrpN] [nvarchar](15) NULL,
[HSTDCHOICE] [smallint] NULL,
[OWNNAME] [nvarchar](35) NULL,
[OWNADR1] [nvarchar](35) NULL,
[OWNADR2] [nvarchar](35) NULL,
[OWNADR3] [nvarchar](35) NULL,
[OWNZIP5] [int] NULL,
[EmgNum] [nvarchar](8) NULL,
[ClassCd1] [smallint] NULL,
[HSTDCODE] [smallint] NULL,
[ClassCd2] [smallint] NULL,
[HSTDCODE2] [smallint] NULL,
[CLASSCODE3] [smallint] NULL,
[HSTDCODE3] [smallint] NULL,
[LANDEST] [int] NULL,
[BUILDING] [int] NULL,
[TaxbLanValu] [int] NULL,
[TaxblbBuild] [int] NULL,
[NETTAX] [numeric](38, 8) NULL,
[TotSPecAsm] [numeric](38, 8) NULL,
[DFRTYPE] [nvarchar](2) NULL,
[SCHLDIST] [smallint] NULL,
[STR] [int] NULL,
[DeededAcre] [numeric](38, 8) NULL,
[DSDESC] [nvarchar](45) NULL,
[LAKENBR] [int] NULL,
[LKLAKD] [nvarchar](35) NULL,
[PLDESC] [nvarchar](45) NULL,
[TWPCITY] [smallint] NULL,
[ETENTD] [nvarchar](25) NULL,
[DFRNEWIMP] [int] NULL,
[ADDR_1] [nvarchar](35) NULL,
[CITY] [nvarchar](25) NULL,
[ZipCode5] [int] NULL,
[CRV_NBR] [int] NULL,
[PRI_CLASS] [smallint] NULL,
[MultiParcl] [nvarchar](1) NULL,
[SALE_DATE] [int] NULL,
[PUR_PRICE] [int] NULL,
[AudFilDate] [int] NULL,
[CUREMV] [int] NULL,
[NBHD_CODE] [nvarchar](8) NULL,
[RJCODE] [smallint] NULL,
[PCCODE] [smallint] NULL,
[SaleRatio] [numeric](38, 8) NULL,
[GlobalID] [uniqueidentifier] NOT NULL,
[Shape] [int] NULL
) ON [PRIMARY]
GO
USE [KoochData]
GO
/****** Object: Table [dbo].[Ranieranneaxparcels] Script Date: 03/08/2012 09:46:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ranieranneaxparcels](
[OLD_PARCELNBR] [nvarchar](255) NULL,
[NEW_PID] [nvarchar](255) NULL,
[OWNNAME] [nvarchar](255) NULL,
[OWNADR1] [nvarchar](255) NULL,
[OWNADR2] [nvarchar](255) NULL,
[OWNADR3] [nvarchar](255) NULL,
[OWNADR4] [nvarchar](255) NULL,
[OWNZIP5] [float] NULL,
[F9] [nvarchar](255) NULL,
[STREET_NAME] [nvarchar](255) NULL,
[STREET__DIR_SFX] [nvarchar](255) NULL,
[CITY] [nvarchar](255) NULL,
[ZIP_CODE_5] [float] NULL
) ON [PRIMARY]
GO
Need some data too?
Thanks
Quinn
March 8, 2012 at 9:22 am
At least a few rows for each table would make it a lot easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 12:31 pm
Here is some data too.
INSERT INTO vectorreplica.dbo.parcels (OBJECTID, TOWN, rang, sect, parcel_id, pidcode, pidcode2,
water, hosp, fire, amb, comdis1, SCHLDIST, gisacres, taxyear, parclnbr, MultPrpN, HSTDCHOICE,
ownname, ownadr1, ownadr2, ownadr3, ownadr4, ownzip5, emgnum, classcd1, hstdcode, classcd2,
hstccode, classcode3, hstdcode3, landest, building, TaxbLanValu, TaxblbBuild, nettax, TotSPecAsm,
dfrtype, SCHLDIST, str, deededacre, DSDESC, lakenbr, LKLAKD, plddesc, twncity, ETENTD, DFRNEWIMP, addr_1, city, zipcode5, crv_nbr, pri_class, multiprcl, sale_date, pur_price, objectid,
comdis1 )
SELECT '1','160.00000000','29.00000000','5.00000000','78-005-00200','PID Present',' ','0','0','4','3','5','363','77.57563306','2011','78-005-00200','78-005-00200','0','OLSEN, GERALD & SUSAN',[661 TOWN ROAD 134 NORTH],'BAUDETTE, MN 56623','','56623','','101','1','0','0','0','0','5' UNION ALL
SELECT '2','160.00000000','29.00000000','4.00000000','78-004-00110','PID Present',' ','0','0','4','3','5','363','3.35541364','2011','78-004-00110','78-004-00110','0','OSE, KENDELL & KAREN',[5877 QUALE AVE NE],'ST. MICHAEL, MN 55376','','55376','','151','0','111','0','0','0','5' UNION ALL
SELECT '3','160.00000000','29.00000000','4.00000000','78-004-00100','PID Present',' ','0','0','4','3','5','363','35.63163439','2011','78-004-00100','78-004-00100','0','COUDRON, ELSIE',[3076 330 AV],'MARSHALL, MN 56258','','56258','','151','0','111','0','0','0','5' UNION ALL
SELECT '4','160.00000000','29.00000000','5.00000000','78-005-31400','PID Present',' ','0','0','4','3','5','363','10.09274215','2011','78-005-31400','78-005-31400','0','STRANGE, KIMBERLY & BRET',[13943 FLINTWOOD DR],'BAXTER, MN 56425-2218','','56425','','111','0','0','0','0','0','5' UNION ALL
SELECT '5','160.00000000','29.00000000','5.00000000','78-005-31000','PID Present',' ','0','0','4','3','5','363','59.68303297','2011','78-005-31000','78-005-31000','0','PAHLEN, PERSKY & VASQUEZ',[],'','','0','','111','0','0','0','0','0','5' UNION ALL
INSERT INTO ranierannexparcels (old_parcelnbr, mew_pid, ownname, OWNADR1,
OWNADR2, OWNADR3, OWNADR4, ownzip5, street_name, STREET__DIR_SFX, city, zip_code_5)
SELECT '15-030-00010','98-051-00010','DULUTH, WINNIPEG & PACIFIC RAILWAY','17641 SOUTH ASHLAND AVENUE','HOMEWOOD, IL 60430','','','60430','','','','0' UNION ALL
SELECT '15-030-00300','98-051-00300','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0' UNION ALL
SELECT '15-030-00310','98-051-00310','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0' UNION ALL
SELECT '15-030-00320','98-051-00320','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0' UNION ALL
Don't shoot your eye out.
Thanks
Quinn
March 13, 2012 at 7:42 am
This is really weird.
I an get records from vectorreplica.parcels using a parcel_id from ranierannexparcels. But can't do the inner join
select * from VectorReplica.dbo.PARCELs where PARCEL_ID = '15-030-34000'
This is getting frustrating.
Quinn
March 13, 2012 at 7:48 am
I would be willing to help but first I need some sample data that I can use. I can't insert the sample data you provided. The columns do not match the data for either table and the first one has some syntax errors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 8:10 am
Your data sets cannot be inserted to the tables created by provided DDL's. After I've spend time to make it run, I found that your data sample is absolute useless anyway as there are no intersections between two sets on relevant columns!
You should be more accurate when posting your ddl and data. We are not paid here, yet 🙂
I've review your question and may be found your problem. You want to update ParcelId into new one where the old is used? If so, your update query should join not on NEW_PID but on the OLD_PARCELNBR:
UPDATE P
SET PARCEL_ID = R.NEW_PID
FROM VectorReplica.dbo.PARCELS AS P
JOIN Ranieranneaxparcels AS R
ON R.OLD_PARCELNBR = P.PARCEL_ID
March 13, 2012 at 10:37 am
Did I miss anything on the etiquette page?
I was able to creat and load on of the 2 tables.
Thanks
Quinn
March 13, 2012 at 10:53 am
Quinn-793124 (3/13/2012)
Did I miss anything on the etiquette page?I was able to creat and load on of the 2 tables.
Thanks
Quinn
No your sample data as you posted will not work. You posted the two create tables and sample data.
Here is your parcels tables.
CREATE TABLE [dbo].[PARCELS](
[OBJECTID] [int] NOT NULL,
[TOWN] [numeric](38, 8) NULL,
[RANG] [numeric](38, 8) NULL,
[SECT] [numeric](38, 8) NULL,
[PARCEL_ID] [nvarchar](12) NULL,
[PIDCODE] [nvarchar](12) NULL,
[PIDCODE2] [nvarchar](30) NULL,
[Water] [smallint] NULL,
[Hosp] [smallint] NULL,
[Fire] [smallint] NULL,
[Amb] [smallint] NULL,
[ComDis1] [smallint] NULL,
[SchD1] [nvarchar](4) NULL,
[GISAcres] [numeric](38, 8) NULL,
[TAXYEAR] [smallint] NULL,
[ParclNbr] [nvarchar](15) NULL,
[MultPrpN] [nvarchar](15) NULL,
[HSTDCHOICE] [smallint] NULL,
[OWNNAME] [nvarchar](35) NULL,
[OWNADR1] [nvarchar](35) NULL,
[OWNADR2] [nvarchar](35) NULL,
[OWNADR3] [nvarchar](35) NULL,
[OWNZIP5] [int] NULL,
[EmgNum] [nvarchar](8) NULL,
[ClassCd1] [smallint] NULL,
[HSTDCODE] [smallint] NULL,
[ClassCd2] [smallint] NULL,
[HSTDCODE2] [smallint] NULL,
[CLASSCODE3] [smallint] NULL,
[HSTDCODE3] [smallint] NULL,
[LANDEST] [int] NULL,
[BUILDING] [int] NULL,
[TaxbLanValu] [int] NULL,
[TaxblbBuild] [int] NULL,
[NETTAX] [numeric](38, 8) NULL,
[TotSPecAsm] [numeric](38, 8) NULL,
[DFRTYPE] [nvarchar](2) NULL,
[SCHLDIST] [smallint] NULL,
[STR] [int] NULL,
[DeededAcre] [numeric](38, 8) NULL,
[DSDESC] [nvarchar](45) NULL,
[LAKENBR] [int] NULL,
[LKLAKD] [nvarchar](35) NULL,
[PLDESC] [nvarchar](45) NULL,
[TWPCITY] [smallint] NULL,
[ETENTD] [nvarchar](25) NULL,
[DFRNEWIMP] [int] NULL,
[ADDR_1] [nvarchar](35) NULL,
[CITY] [nvarchar](25) NULL,
[ZipCode5] [int] NULL,
[CRV_NBR] [int] NULL,
[PRI_CLASS] [smallint] NULL,
[MultiParcl] [nvarchar](1) NULL,
[SALE_DATE] [int] NULL,
[PUR_PRICE] [int] NULL,
[AudFilDate] [int] NULL,
[CUREMV] [int] NULL,
[NBHD_CODE] [nvarchar](8) NULL,
[RJCODE] [smallint] NULL,
[PCCODE] [smallint] NULL,
[SaleRatio] [numeric](38, 8) NULL,
[GlobalID] [uniqueidentifier] NOT NULL,
[Shape] [int] NULL
) ON [PRIMARY]
Here is the insert...
INSERT INTO vectorreplica.dbo.parcels (OBJECTID, TOWN, rang, sect, parcel_id, pidcode, pidcode2,
water, hosp, fire, amb, comdis1, SCHLDIST, gisacres, taxyear, parclnbr, MultPrpN, HSTDCHOICE,
ownname, ownadr1, ownadr2, ownadr3, ownadr4, ownzip5, emgnum, classcd1, hstdcode, classcd2,
hstccode, classcode3, hstdcode3, landest, building, TaxbLanValu, TaxblbBuild, nettax, TotSPecAsm,
dfrtype, SCHLDIST, str, deededacre, DSDESC, lakenbr, LKLAKD, plddesc, twncity, ETENTD, DFRNEWIMP, addr_1, city, zipcode5, crv_nbr, pri_class, multiprcl, sale_date, pur_price, objectid,
comdis1 )
SELECT '1','160.00000000','29.00000000','5.00000000','78-005-00200','PID Present',' ','0','0','4','3','5','363','77.57563306','2011','78-005-00200','78-005-00200','0','OLSEN, GERALD & SUSAN',[661 TOWN ROAD 134 NORTH],'BAUDETTE, MN 56623','','56623','','101','1','0','0','0','0','5' UNION ALL
SELECT '2','160.00000000','29.00000000','4.00000000','78-004-00110','PID Present',' ','0','0','4','3','5','363','3.35541364','2011','78-004-00110','78-004-00110','0','OSE, KENDELL & KAREN',[5877 QUALE AVE NE],'ST. MICHAEL, MN 55376','','55376','','151','0','111','0','0','0','5' UNION ALL
SELECT '3','160.00000000','29.00000000','4.00000000','78-004-00100','PID Present',' ','0','0','4','3','5','363','35.63163439','2011','78-004-00100','78-004-00100','0','COUDRON, ELSIE',[3076 330 AV],'MARSHALL, MN 56258','','56258','','151','0','111','0','0','0','5' UNION ALL
SELECT '4','160.00000000','29.00000000','5.00000000','78-005-31400','PID Present',' ','0','0','4','3','5','363','10.09274215','2011','78-005-31400','78-005-31400','0','STRANGE, KIMBERLY & BRET',[13943 FLINTWOOD DR],'BAXTER, MN 56425-2218','','56425','','111','0','0','0','0','0','5' UNION ALL
SELECT '5','160.00000000','29.00000000','5.00000000','78-005-31000','PID Present',' ','0','0','4','3','5','363','59.68303297','2011','78-005-31000','78-005-31000','0','PAHLEN, PERSKY & VASQUEZ',[],'','','0','','111','0','0','0','0','0','5' UNION ALL
Ok so I removed the union all from the last line. Then added '' around all the fields that have [].
Here is the insert after that.
INSERT INTO parcels (OBJECTID, TOWN, rang, sect, parcel_id, pidcode, pidcode2,
water, hosp, fire, amb, comdis1, SCHLDIST, gisacres, taxyear, parclnbr, MultPrpN, HSTDCHOICE,
ownname, ownadr1, ownadr2, ownadr3, ownadr4, ownzip5, emgnum, classcd1, hstdcode, classcd2,
hstccode, classcode3, hstdcode3, landest, building, TaxbLanValu, TaxblbBuild, nettax, TotSPecAsm,
dfrtype, SCHLDIST, str, deededacre, DSDESC, lakenbr, LKLAKD, plddesc, twncity, ETENTD, DFRNEWIMP, addr_1, city, zipcode5, crv_nbr, pri_class, multiprcl, sale_date, pur_price, objectid,
comdis1 )
SELECT '1','160.00000000','29.00000000','5.00000000','78-005-00200','PID Present',' ','0','0','4','3','5','363','77.57563306','2011','78-005-00200','78-005-00200','0','OLSEN, GERALD & SUSAN','[661 TOWN ROAD 134 NORTH]','BAUDETTE, MN 56623','','56623','','101','1','0','0','0','0','5' UNION ALL
SELECT '2','160.00000000','29.00000000','4.00000000','78-004-00110','PID Present',' ','0','0','4','3','5','363','3.35541364','2011','78-004-00110','78-004-00110','0','OSE, KENDELL & KAREN','[5877 QUALE AVE NE]','ST. MICHAEL, MN 55376','','55376','','151','0','111','0','0','0','5' UNION ALL
SELECT '3','160.00000000','29.00000000','4.00000000','78-004-00100','PID Present',' ','0','0','4','3','5','363','35.63163439','2011','78-004-00100','78-004-00100','0','COUDRON, ELSIE','[3076 330 AV]','MARSHALL, MN 56258','','56258','','151','0','111','0','0','0','5' UNION ALL
SELECT '4','160.00000000','29.00000000','5.00000000','78-005-31400','PID Present',' ','0','0','4','3','5','363','10.09274215','2011','78-005-31400','78-005-31400','0','STRANGE, KIMBERLY & BRET','[13943 FLINTWOOD DR]','BAXTER, MN 56425-2218','','56425','','111','0','0','0','0','0','5' UNION ALL
SELECT '5','160.00000000','29.00000000','5.00000000','78-005-31000','PID Present',' ','0','0','4','3','5','363','59.68303297','2011','78-005-31000','78-005-31000','0','PAHLEN, PERSKY & VASQUEZ','[]','','','0','','111','0','0','0','0','0','5'
Here is the results of this insert.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ownadr4'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'hstccode'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'plddesc'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'twncity'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'multiprcl'.
Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
OK so let's remove the column names in the insert...
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Now for the second table. I changed the table name in the insert to match the table name in the ddl. Then removed the trailing UNION ALL.
INSERT INTO Ranieranneaxparcels (old_parcelnbr, mew_pid, ownname, OWNADR1,
OWNADR2, OWNADR3, OWNADR4, ownzip5, street_name, STREET__DIR_SFX, city, zip_code_5)
SELECT '15-030-00010','98-051-00010','DULUTH, WINNIPEG & PACIFIC RAILWAY','17641 SOUTH ASHLAND AVENUE','HOMEWOOD, IL 60430','','','60430','','','','0' UNION ALL
SELECT '15-030-00300','98-051-00300','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0' UNION ALL
SELECT '15-030-00310','98-051-00310','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0' UNION ALL
SELECT '15-030-00320','98-051-00320','ARASON, GERALD','120 PARK AVE','INTL FALLS, MN 56649','','','56649','','','','0'
results...
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mew_pid'.
So again I removed the column listing...
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
There is no way that given what you posted anybody can load these tables with anything.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 10:57 am
Quinn-793124 (3/13/2012)
Did I miss anything on the etiquette page?I was able to creat and load on of the 2 tables.
Thanks
Quinn
No, you didn't! However, if you try to populate data in the tables using script you've posted, the script produces errors, as, for some reason, it contains less columns than specified in table's DDL. Your data script should not just SELECT data but INSERT it into your tables!
Also, as I've said before, the data posted doesn't not intersect, so it's useless. For your case, you should provide at least some data which is "joinable"...
Anyway, have you tried the query I've posted?
March 13, 2012 at 10:57 am
What would help is if you have (or create) a sandbox database where you can test your scripts outside of your database to ensure that they work before posting them.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply