Problem inserting Rows after table creation

I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.

It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?

Thanks,

Dave

TABLE GENERATION SCRIPT

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO

CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

TABLE POPULATION SCRIPT

INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)I wonder if it has anything to do with the fact that, in your where clause, you're excluding records that exist in the very table you're inserting into (don't know why you're doing that, but I'm sure you have your reasons). Have you tried removing that last clause to see if it will work?|||The reason I am excluding records that exist in the table is b/c I am trying to perform a sort of "Merge" where only new records get inserted.

Is there a better way of doing this?|||How about inserting existing rows into a temp table first.
Then run you main select, but where records don't exist in your temp table.

Insert into #myTemp (select cast(....) )

insert into tbCelebroAds(....)select ...
where cast(...) not in select * from #myTemp|||Thanks TJ, that is a simple solution that worked perfectly when I tried it.

Thanks again for your help!

Problem INSERTING duplicate Rows

I'm trying to populate a temporary table with unique data, however my
populating query produces duplicated data. I've tried using a sub query
(i.e. querying the temp table during the INSERT to see if the data
already exists), but that doesn't stops the primary key violation
error. I've included the code below. tblSearchProduct stores a list of
words, along with the ID of the product title that word appears in. I'm
doing a search on products with certain words in the titles, so
therefore don't want the same product to appear multiple times. Any
suggestions? Ideally I want to tell SQL Server to just ignore the error
and move onto the next record in the result set, but I'm not sure how
to achieve that.
CREATE TABLE #tResults (
Position int NOT NULL identity(1,1),
[ID] varchar(50) NOT NULL,
PRIMARY KEY([ID])
)
INSERT INTO #tResults([ID])
SELECT s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductNameand...@.thevalley.f9.co.uk wrote:
> I'm trying to populate a temporary table with unique data, however my
> populating query produces duplicated data. I've tried using a sub
query
> (i.e. querying the temp table during the INSERT to see if the data
> already exists), but that doesn't stops the primary key violation
> error. I've included the code below. tblSearchProduct stores a list
of
> words, along with the ID of the product title that word appears in.
I'm
> doing a search on products with certain words in the titles, so
> therefore don't want the same product to appear multiple times. Any
> suggestions? Ideally I want to tell SQL Server to just ignore the
error
> and move onto the next record in the result set, but I'm not sure how
> to achieve that.
> CREATE TABLE #tResults (
> Position int NOT NULL identity(1,1),
> [ID] varchar(50) NOT NULL,
> PRIMARY KEY([ID])
> )
> INSERT INTO #tResults([ID])
>
> SELECT s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName
Try the following select stmt.

> SELECT distinct s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName|||SELECT distinct s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductName|||for some reason the update query is not appearing, anyway try distinct
after select, like
select distinct s.[ID]|||INSERT INTO #tResults([ID])
SELECT DISTINCT S.[id]
FROM ... etc
You don't need ORDER BY. It does nothing useful for you.
David Portas
SQL Server MVP
--|||This is not a RDBMS design at all, but a sequential file system in a
bad disguise. Stop using the "universal magical" VARCHAR(50) when you
know it is oversized. Stopping putting silly redundant "t_" and "tbl_"
prefixes on table names in violation of ISO-11179. Stop using IDENTITY.
Why did you put an ORDER BY on an INSERT INTO statement? Tables are
not sequential files; they have no ordering.
CREATE TABLE #Results
(product_id CHAR(13) NOT NULL PRIMARY KEY); -- I'll guess you use EAN
INSERT INTO #Results(product_id)
SELECT DISTINCT S.product_id
FROM SearchProducts AS S,
ContractProducts AS C,
Products AS P
WHERE P.word LIKE '% the %'
AND P.product_id = C.product_id
AND S.product_id = C.product_id;
But now I have to ask, why are SearchProducts, ContractProducts and
mere plain old Products logically different? I'll bet they are all the
same kind of entities, but you just put them into different tables
based on some status. This how we did it with punch cards and tape
files. Likewise, why is the same data element called ID, product_id
and contract_product_id in the same schema?
I think you need some serious help with database design.