How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to Import from flat file and update DateTime column?

I have a a flat file that consists of 2 Columns of data that need to overwrite an existing Table that has 3 Columns of data. The Import fails because the 3rd column on the table is a Date stamp column with the Data Type of "smalldatetime" and does not allow Null data. If I were to delete this 3rd column from the table the import works great but I lose the DateTime column. How can I use the Import Wizard to import the first 2 columns from a text file and update the 3rd column with the date and time? The wizard does not seem to let me update a column unless the data for this column comes from the flat file. Please assist, thanx.

Which wizard are you talking about? The import / export data wizard? If so this wizard is for appending new rows or deleting existing rows. I do not believe that it is really meant for updating rows (i.e. overwriting). You would want to create an SSIS package and use the OLE DB Command instead of the OLE DB Source to overwrite a row.

|||

If you are looking to import data, you can use the wizard to build the package, then save it and open it in Business Intelligence Developer Studio. If you then open the data flow, you can add a Derived Column task, add a new column to it, and use the GETDATE() function to return the current date.

|||If he did what you suggest wouldn't he still just be importing new records instead of overwriting the existing records?

|||

I believe the import/export wizard gives you the ability of providing a query for the import. Perhaps you can write a where you have a 3rd columns with the getdate(if using sql server) or sysdate(for Oracle) or any other DB function that retrieves the current date.

|||From his description, I wasn't positive that he wanted to "update" existing records as much as he was looking to truncate and reload the table. Using the wizard (and setting the create destination table options) will drop and recreate the table before loading it. However, I might have his needs wrong. Maybe he'll respond and let us know if either approach was successful.

|||

I'm sure the OP will realise that when the going gets tough, the tough-SSIS-community-contributors get going.

Sorry.