bulk insert which check if record exists

Hi All,
I want to bulk insert from table A to table B (both table have the same
design fields which contain primary keys). Is there a way to check if record
exist before inserting rows to avoid primary key violation? What would be my
query?
thanks in advance,
joelTry something like this:
insert tableA (<cols> )
select b.<cols>
from tableA b
left outer join tableA a
on b.PrimaryKey = a.PrimaryKey
where a.PrimaryKey IS NULL|||If you assume that ID is your PK then you can do something like this
INSERT INTO TABLEB
SELECT A.*
FROM TABLEA A
LEFT JOIN TABLEB B
ON A.ID = B.ID
WHERE B.ID IS NULL
http://sqlservercode.blogspot.com/|||Sorry, tableA and tableB are backwards in my example. This will insert
into tableA, rows that are in tableB and not in tableA.
insert tableB (<cols> )
select a.<cols>
from tableA a
left outer join tableB b
on b.PrimaryKey = a.PrimaryKey
where b.PrimaryKey IS NULL|||Sorry again, untested code bites again.
This will insert
into tableB, rows that are in tableA and not in tableB.
insert tableB (<cols> )
select a.<cols>
from tableA a
left outer join tableB b
on b.PrimaryKey = a.PrimaryKey
where b.PrimaryKey IS NULLsql

BULK INSERT maybe ??

I have a directory with files:

X.dbf
X.mdx
Y.dbf
Y.mdx
...

These files contain updates for my DB (I don't know their structure). How I can insert them in temporary tables on the SQL server ?

Note: I don't want to use Import/Export Tool, cause I will need this insert as scripts...I'd use DTS.

-PatP|||I'd use DTS.

-PatP

You would?

I'd create a sproc...

USing xp-Cmdshell, I would interogate the directory and load the file names to a table.

I would process the files 1 by 1

I would the bcp the data in to a single column varchar table

I would then process the data.

Bu if you don't know the structure of the data, what would you propose you'd do?|||Last time I did something like this, the .dbf extension files came from dBase V. DTS has an interface for that datafile type. As for .mdx ... ?|||It isn't hard to determine the schema of a DBF file, as Tom pointed out they are just dBase files which are effectively a single table with the schema tucked into the file header. You can relatively easily import arbitrary dbf files into a database from within a DTS package... It is more complex than dealing with a static structure, but not rocket science by any means.

The MDX files are just queries written as Multi-dimensional Expressions. Those can simply be stuffed into a TEXT column somewhere, probably the best organization would be to simply track what file they came from (X.MDX) and the text in a single table, maybe with some decorative columns to track when the file was timestamped, when it was imported into the table, etc.

-PatP|||Multi-dimensional Expressions? I may be way off, but I think I remember MDX to be primary indexes and NDX secondary (?)...Or maybe it's FoxPro? It's been awhile.|||I should just make it part of my sig...

"It really helps if you read these things"|||...and your point is...?

Buld Data load import

HI

I have a table XYZ that needs to contain a million records as operational data.

XYZ has a column named SLOT_VALUE which has values 1,2,3....100,000

What is the easiset way to bulk load this information in the shortest possible time...

Insert scripts/ Batch program like if..while loop takes heck lot of time....

Hello,

If you mean that you just want to populate that table with incrementing values from 1 to 1MIL, then the easiest would be to execute something like

select top 1000000 identity(int,1,1) as Num
into TempNumTable
from syscolumns c1
cross join syscolumns c2
cross join syscolumns c3
cross join syscolumns c4
... etc

You can't just directly insert into your already created table as the identity() function need a select into clause. You can just insert from the newly created table into your XYZ operational table.

If this is not what you mean, and you have a flat file that you want to bulk load into the table, have a look at the BULK INSERT command (use TABLOCK and consider dropping any existing indexes on the table prior to the load).

Cheers,

Rob

|||

Guess the easiest is to have t-sql script using a while loop...I thought of bcp but then I need to create a file...that would be redundant...

Anyhow thnx for the post.

|||

No worries. Just remember that performing this operation within a while loop will entail 1 million individual inserts! The set based solution described above would be much faster and much more efficient. If this is a once-off operation, then it's probably not an issue.

Cheers,
Rob