Am having script Problems with Duplicates and Inserts

Ok, here is the situation, I have a view in one database and I want to insert all the data into a table on the same server but in a different database. With a no duplicate insert, cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

So I first wrote a script that looked for duplicates, this worked.

FROM Coffee.dbo.vueProductCase a
JOIN (SELECT ProductCode, COUNT(*) AS cnt
FROM coffee.dbo.vueProductCase
GROUP BY ProductCode
HAVING COUNT(*) > 1) b
ON a.ProductCode = b.ProductCode

It displayed a list of Duplicates, so I then tried to enter this script which doesnt seem to work at all, but it could be that it is because I dont know how to combine the scripts to insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

insert dbo.tblInItem
(ItemId,Descr,ProductLine,SalesCat,UomBase,UomDflt )
select
t1.ProductCode,
t1.[Description],
t1.'COFFEE',
t1.'CS',
t1.WeightMeasurement,
t1.'EACH'
from COFFEE.dbo.vueProductCase t1 left join dbo.tblInItem t2 on t1.ProductCode = t2.itemid
where t2.itemid is null

Can I get some help please??cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

Doesn't make sense.

insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

Nope...this one doesn't make sense either.

Give us a sample table create statement with insert statements for the data. Then, show us what you want the data to look like when complete. We should be able to help you pretty quickly then. Right now, someone else might be able to help you if they understand you. I'm not getting it though. [:)]|||My question is "What error message or incorrect results are you getting".

Because I don't see anything syntactically wrong with your insert statement, and "It don't work fer nuffin at all" doesn't give us a lot of clues...

alternatives to adjacency model? (hierarchical data)

Hello Gurus,
I am looking for a hierarchical modeling technique other than
adjacency.
Simplicity is the goal. Updates and inserts must be reasonably easy.
For example, I'd like to see some modeling options other other this:
CREATE TABLE [Hier] (
[ID] [int] NOT NULL ,
[PARENT_ID] [int] NULL ,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Parent] FOREIGN KEY
(
[PARENT_ID]
) REFERENCES [Hier] (
[ID]
)
)
Anyone know of any (links are often helpful)?
Thank you,
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1123788975.065397.6950@.g47g2000cwa.googlegroups.com...
> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
>
Well, there's the nested set model. But it simplifies nested searching and
complicates inserts and updates.
For instance in this example
http://groups-beta.google.com/group...b60b7151?hl=en&
This is required to insert a node
BEGIN
DECLARE right_most_sibling INTEGER;
SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = :your_boss);
UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;
INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling, (right_most_sibling + 1))
END;
And you probably should introduce a transaction and take a TABLOCKX on
Personnel to control concurrency.
David|||Your model lacks circular reference prevention. Consider something like the
solution suggested in this thread:
http://msdn.microsoft.com/newsgroup...891d&sloc=en-us
ML|||Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4
SQL Lessons
http://www.dbmsmag.com/9604d06.html
Maintaining Hierarchies
http://www.windowsitpro.com/Article...=glance&s=books
AMB
"n_o_s_p_a__m@.mail.com" wrote:

> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
> For example, I'd like to see some modeling options other other this:
> CREATE TABLE [Hier] (
> [ID] [int] NOT NULL ,
> [PARENT_ID] [int] NULL ,
> CONSTRAINT [PK] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Parent] FOREIGN KEY
> (
> [PARENT_ID]
> ) REFERENCES [Hier] (
> [ID]
> )
> )
> Anyone know of any (links are often helpful)?
> Thank you,
> -KJ
>|||Go out and buy a copy of TREES & HIERARCHIES IN SQL. It will save you
a lot of trouble and pay my mortgage.

Alternative to using USE in a PROCEDURE

is there a way to get around not using USE in a PROCEDURE?

I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.

thx in advanceshould i use the master database and prefix my procedures with sp_?|||Huh second time in 2 mins. Use the fully qualified path to the entity

<database>.<owner>.<entity>

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass withou
t
having to insert/update each row individually. You could use DTS, BULK INSER
T
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:

> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are the
re
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.

> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.

> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT[vbcol=seagreen]
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
>
Stored[vbcol=seagreen]
there[vbcol=seagreen]|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:

> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
> staging
> without
> INSERT
> Stored
> there
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
>
from[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
Inserts/[vbcol=seagreen]
Are[vbcol=seagreen]|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:

> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> from
> to
> the
> Inserts/
> Are
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.

>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
the[vbcol=seagreen]
> staging
> without
> INSERT
Inserts/[vbcol=seagreen]
> Stored
> there
>

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass without
having to insert/update each row individually. You could use DTS, BULK INSERT
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:
> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.
> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.
> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
> > SQL2K SP4
> >
> > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > Updates/ Deletes and imports them into a SQL DB every day. I have a
Stored
> > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
there
> > any alternatives to this from within SQL Server?
> >
> > TIA, ChrisR
> >
> >
> >|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
> > I appreciare your ideas, but Im confused.
> >
> > > You could write your own application to parse the file and run each
> > command
> >
> > I really need to do this from within SQL Server.
> >
> > > If you just provided a data file and loaded it into a staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> >
> > How would this be any better than what I have? I still need to get it
from
> > the file into a table. The way Im reading this, I need to go from file
to
> > table, then from table to table?
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > Hi Chris
> > >
> > > You could write your own application to parse the file and run each
> > command,
> > > a different alternative would be to load the data into a table and use
the
> > > EXEC command to run them. As your statements are in a file, there is a
> > > security risk. If you just provided a data file and loaded it into a
> > staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> > >
> > > John
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K SP4
> > > >
> > > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > Stored
> > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
Are
> > there
> > > > any alternatives to this from within SQL Server?
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:
> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> > Hi Chris
> >
> > How do you create the file that makes up these SQL Statements?
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > I appreciare your ideas, but Im confused.
> > >
> > > > You could write your own application to parse the file and run each
> > > command
> > >
> > > I really need to do this from within SQL Server.
> > >
> > > > If you just provided a data file and loaded it into a staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > >
> > > How would this be any better than what I have? I still need to get it
> from
> > > the file into a table. The way Im reading this, I need to go from file
> to
> > > table, then from table to table?
> > >
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > > Hi Chris
> > > >
> > > > You could write your own application to parse the file and run each
> > > command,
> > > > a different alternative would be to load the data into a table and use
> the
> > > > EXEC command to run them. As your statements are in a file, there is a
> > > > security risk. If you just provided a data file and loaded it into a
> > > staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > > >
> > > > John
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K SP4
> > > > >
> > > > > Howdy all. I have a daily process that gets a .txt file full of
> Inserts/
> > > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > > Stored
> > > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
> Are
> > > there
> > > > > any alternatives to this from within SQL Server?
> > > > >
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use
the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>