September 28, 2010 at 7:53 am
Here is my SQL that I'm trying to accomplish. Obviosuly it doesn't work. My goal is to "Print" or notify myself that if the serial number exists, then what is that serial number is.
IF EXISTS ( SELECT COUNT(*)
FROM dbo.DME_ITEM dme INNER JOIN dbo.TempMSD TM ON TM.SerialNumber = dme.SERIAL_NUMBER
WHERE dme.SERIAL_NUMBER = TM.SerialNumber
)
BEGIN
PRINT dbo.DME_ITEM.SERIAL_NUMBER +'-'+ 'Serial Number Already Exists'
END
ELSE
PRINT 'doesnt exist'
When I run this code, I get the following message:
Msg 128, Level 15, State 1, Line 7
The name "dbo.DME_ITEM.SERIAL_NUMBER" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Thank you
September 28, 2010 at 8:05 am
you are really close...your problem is that you can't just refer to a column of data..it has to come from a select statement.
so you are testing if the serial number exists, but not capturing it anywhere, so you cannot print it/email it etc.
here's how i would do what you are after:
declare @TheSerialNumber varchar(100)
SELECT
@TheSerialNumber = dbo.DME_ITEM.SERIAL_NUMBER
FROM dbo.DME_ITEM dme
INNER JOIN dbo.TempMSD TM
ON TM.SerialNumber = dme.SERIAL_NUMBER
WHERE dme.SERIAL_NUMBER = TM.SerialNumber
IF @TheSerialNumber IS NOT NULL
BEGIN
PRINT @TheSerialNumber +'-'+ 'Serial Number Already Exists'
END
ELSE
PRINT 'doesnt exist'
Lowell
September 28, 2010 at 8:26 am
Excellent... works like a charm. Thank you so much for the help...
The only thing I had to alter was the...
SELECT @SerialNumber = dbo.DME_ITEM.SERIAL_NUMBER
Changed to...
SELECT @SerialNumber = dme.SERIAL_NUMBER
To fix...
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dbo.DME_ITEM.SERIAL_NUMBER" could not be bound.
September 28, 2010 at 9:19 am
Thank you again Lowell for the help. If you don't mind, I would like to tap into your brain one more time on this statement.
Let me lay the ground work. I have a bulk insert (~10k records) of data going into the TempMSD table. I need to insert that data into the DME_ITEM table. However, this is the second run at this data load. The first run only had ~5k records and I ran the bulk insert directly into the DME_ITEM table. The bulk insert into the TempMSD table has ~5k records that already exist in the DME_ITEM table. I don't want to duplicate the entries. I think that's why I started with the IF EXISTS in my original statement, becasue I knew I would be eventually heading down that path.
With that being said, can I just repalce the ELSE PRINT 'doesnt exist' with ELSE INSERT INTO DME_ITEM (columns) VALUES (...)? I don't think it would be that easy.
I use this script for entering users into the system. Could we use some variation of this to make the script work?
IF (SELECT COUNT(*) FROM USER_TABLE WHERE USER_NUMBER = 'Something') = 0
INSERT INTO USER_TABLE
SELECT REPLACE(NEWID(), '-', '')
,'Something'
,[FIELD]
,'User Name'
,[FIELD]
,[FIELD]
FROM USER_TABLE
WHERE USER_NUMBER = 'User Template'
What I like about this script is, if the user exisits, it skips. This is what I need in my script, if the DME_ITEM.SERIAL_NUMBER exists, skip and enter the next...
Thank you and so sorry for the lengthy post.
September 28, 2010 at 9:31 am
Doc what you want to do is the insert as a single SET based operation, which might insert 5K's worth of records, all in one fell swoop;
It's actually easy, it's simply creating an INSERT....SELECT FROM that left outer joins on the destination table.
you could also use the fancy MERGE statement to do the same thing as well, if you want to play with the new syntax.
To give you meaningful, repeatable results, i need the CREATE TABLE definitions of both the destination table and your temp table.
is it ONLY the serial number that makes it unique as to whether we insert or not? not sure from our descriptions so far, so lets do it right.
Lowell
September 28, 2010 at 9:33 am
Doctork11 (9/28/2010)
Thank you again Lowell for the help. If you don't mind, I would like to tap into your brain one more time on this statement.Let me lay the ground work. I have a bulk insert (~10k records) of data going into the TempMSD table. I need to insert that data into the DME_ITEM table. However, this is the second run at this data load. The first run only had ~5k records and I ran the bulk insert directly into the DME_ITEM table. The bulk insert into the TempMSD table has ~5k records that already exist in the DME_ITEM table. I don't want to duplicate the entries.
It sounds like the EXCEPT operator will work just fine for you:
INSERT INTO DME_ITEM (Columns to insert into)
SELECT (Your Columns, same order as above)
FROM TempMSD
EXCEPT
SELECT (Same Columns, same order)
FROM DME_ITEM;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 28, 2010 at 10:37 am
You can also wrap it in a BEGIN TRANSACTION/ROLLBACK TRANSACTION like the following so you can test your script without actually making changes.
(borrowed from WayneS)
BEGIN TRANSACTION
INSERT INTO DME_ITEM (Columns to insert into)
SELECT (Your Columns, same order as above)
FROM TempMSD
EXCEPT
SELECT (Same Columns, same order)
FROM DME_ITEM;
SELECT COUNT(*)
FROM TempMSD
EXCEPT
SELECT (Same Columns, same order)
FROM DME_ITEM;
ROLLBACK TRANSACTION
September 28, 2010 at 12:20 pm
Here are the tables. There is all kinds of constraints on the DME_ITEM table. The user will populate some or most of the data after the load; therefore, I just need to get the basics loaded.
DME_ITEM.DME_TYPE_CODE -> TempMSD.DMEType
DME_ITEM.SERIAL_NUMBER -> TempMSD.SerialNumber
DME_ITEM.BARCODE_SYS_ID -> TempMSD.ScanCode
DME_ITEM.OWNERSHIP_TYPE -> TempMSD.OwnershipType
The TempMSD.AcquiredFrom and ScanCodeCharacter are not necessary in the INSERT because they are used in a concatenation that occurs before the this stage.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply