Insert Records - SQL Server Forums

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

Transact-SQL (2005)

Insert Records

Author

Topic

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 12:56:01

I have two tables.. One File that contains master product information and one file that contains a subset of that information. Lets say both tables only have two fields in the file, ITEM# and YEAR.
What I want to do is insert only those Item# records that are in the master file but not yet in the subset file based on a condition on the year field in the master file. I cant have duplicates.
Thanks

Sample file
Masterfile: Item#, Year
SubsetFile: Item#, Year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:02:42

[code]SELECT m.[ITEM#],m.YEAR
FROM master m
WHERE NOT EXISTS(SELECT 1 FROM [ITEM#] = m.[ITEM#] AND YEAR = m.YEAR)
AND Year=@YourYear
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 13:05:12

Thank you

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 13:08:13

oh Im trying to insert the information from the masterfile into the Subset File where the record doesnt exist in the subset file and also based on a condition
in the master file.
thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:09:11

then


INSERT INTO subset
SELECT m.[ITEM#],m.YEAR
FROM master m
WHERE NOT EXISTS(SELECT 1 FROM [ITEM#] = m.[ITEM#] AND YEAR = m.YEAR)
AND Year=@YourYear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 13:33:04

After Not Exists(Select 1 from [Item#] = m.[item#]... Not clear on the "1" and not clear
afer the "From" usually its a file name after from here its showing the Item# field. Sorry Im confused.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:37:43

[code]INSERT INTO subset
SELECT m.[ITEM#],m.YEAR
FROM master m
WHERE NOT EXISTS(SELECT 1 FROM subset WHERE [ITEM#] = m.[ITEM#] AND YEAR = m.YEAR)
AND Year=@YourYear
[/code]

1 is use for boolean check to see if record is present or not

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-16 : 13:40:31

quote:
Originally posted by jwmayo

After Not Exists(Select 1 from [Item#] = m.[item#]... Not clear on the "1" and not clear
afer the "From" usually its a file name after from here its showing the Item# field. Sorry Im confused.
Thanks

The NOT EXISTS is used to check if the records are present in master before inserting. Only the records not in your subset table will be inserted.

Did you try the query posted by Visakh. Did you get any errors?

Just replace "subset" and "master" with your actual table names and the column names with your actual column names.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 13:48:08

Yes error near = sign..
thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:50:24

can you try my last suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 14:01:49

Ya did that... Sorry I got that working. Part thats not working now is because the real version I need is a bit more complicated below is the statement im using.
I think the issue is that I have a bunch of fields Im trying to insert into [Item master2] and they arent listed after the insert into [Item master2]

INSERT INTO [Item Master2]

SELECT [GPRDDTA_F4101@B].IMLITM,[GPRDDTA_F4101@B].[IMSRP4],[GPRDDTA_F4101@B].[imprp1],[GPRDDTA_F4101@B].[QM@ID1]
FROM [GPRDDTA_F4101@B]
WHERE NOT EXISTS(SELECT 1 FROM [item master2] where [item master2].ISBN=[GPRDDTA_F4101@B].IMLITM)

AND [GPRDDTA_F4101@B].[IMSRP4] not in ('OEI','DAF','OEH','NCM','MLS','PSR','PSF','OIH','MFL','ECK')
or [GPRDDTA_F4101@B].[imprp1] <> '5'
or [GPRDDTA_F4101@B].[QM@ID1] >=110182

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 14:06:08

whats the error you're getting? Make sure number of columns you're trying to insert is same as that in table and also corresponding fields are of same type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 14:09:35

Error: Column name or number of supplied values does not match table definition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 14:12:44

thats obvious. you've more columns specified than that is present in table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 14:15:00

I was pretty sure thats what it was. I only wanted to insert one column of info from the F4101@B file into the [Item Master2] file.. lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 14:16:32

then include that column alone in INSERT and SELECT like

INSERT INTO subset (column)
SELECT columnvalue
FROM master m
WHERE NOT EXISTS(SELECT 1 FROM subset WHERE [ITEM#] = m.[ITEM#] AND YEAR = m.YEAR)
AND Year=@YourYear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources