Temporarily override column restrictions in Bulk Insert

Question

Temporarily override column restrictions in Bulk Insert

Bryan P. Clauss

SSC Eights!

Points: 959
More actions
June 23, 2005 at 2:41 pm

#164955
Is there a way to temporarily override a column that does not allow NULLs in a SQL procedure to allow me to populate the data, and then go back in and put the <Not NULL> data back in the column?
For example (Take my naming convention with a grain of salt. These are not my "real" names, but an example of what I need to do.)
I have a table, tblEmployee, that has a relationship to the table tblNames. See "Table Structure" below.
In order to add an tblEmployee, there needs to be a record with a tblNames out there to put his Name in.
tblNames contains any name (person) in the database, whether they work here, or are a customer. The tblEmployee has a column Employee_Foreign_Key_Name_ID that is required, and has to be in the table tblNames.
Under normal circumstances, when I create an tblEmployee, I add the tblNames at the same time (tblNames is added first in the Code), and there is no problem.
However, I am doing a conversion, and I would rather add the tblEmployee info first (I have a definite Employee_ID that I want to add), and then go back and create the tblNames record.
Since I don't really care what Name ID's I get, I wanted to do the following:
=======================================
```
-- "Override" Identity Seed parameter
SET IDENTITY_INSERT tblEmployee ON
```
```
-- Create "Bulk" of info
Insert 
Into tblEmployee 
 (fields) 
Select  <fields> 
From tbl_Old_Employee_Record 
```
```
-- Loop through records again, and create other records as needed
```
```
Declare Employee Cursor
For Select Employee_ID
 From tbl_Old_Employee_Record 
```
```
Open Employee 
```
```
Fetch Next
From Employee 
Into @Employee_ID
```
```
While @@FETCH_STATUS = 0
 Begin
```
```
  Insert
  Into tblNames
   ( Name)
  Select Name
  From tbl_Old_Employee_Record
  Where Employee_ID = @Employee_ID
```
```
  Update tblEmployee 
  Set Employee_Foreign_Key_Name_ID = @@Identity
  Where Employee_ID = @Employee_ID
```
```
  <do other stuff>
```
```
  Fetch Next
  From Employee 
  Into @Employee_ID
 END
```
```
Close Employee 
```
```
Deallocate Employee 
```
===============================
tbl_Old_Employee_Record - Old record to be converted
-----------------------
Employee_ID
Emp_Name
Emp_Address
Emp_Tax_Info
Emp_HR_Info
EMP_Other_Employee_Info
tblNames
--------
Name_ID Identity
Name_Name
Name_Other_Info
tblHRInfo
---------
HR_ID Identity
HR_Foreign_Key_Employee_ID not Null, relates to tblEmployee
HR_Other_Info
tblEmployee
-----------
Employee_ID Identity
Employee_Foreign_Key_Name_ID not Null, relates to tblNames (Column that I want to override)
Employee_Other_Info

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

During Bulk Insert constraints on the table are not checked for the bulk copy operation unless CHECK_CONSTRAINTS is specified.

So you can bcp out the data and use Bulk Insert to load data.

Or Remove the constraints and apply them back after resolving the data. You may get better solutions from Veterns in this Forum.

Regards,
gova

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 2

I am not exactly doing a "BULK" insert. It is just an "Insert Into" command.

From what I see in the documentation, I can't do a Bulk Insert because I am not filling in all of the fields in my new table, and I am not using all of the fields in my old table.

If there is another way of doing this, please let me know!

Thanks!

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

You can bcp out data from a view or query with just columns and rows you need. Then use format files to load the databack through Bulk Insert.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=193526#bm193537

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=192560#bm192593

I cannot think any other method.

Regards,
gova

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 4

Do you have DEFAULT VALUE for the NOT NULL fields in the table definition?

As govinn mentioned you can use BulkInsert with format file but if you won't have values or defauls for the NOT NULL fields the op will fail

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Yes Bulk Insert and bcp will ignore the foreign Key and check constraints unless specified to check. Only way to get data into a not null columns during bcp is as Vasc mentioned having default values on that column.

Regards,
gova