How To BCP IN To Table With IDENTITY Column
Sep 8, 2005
I have fixed length records (167 bytes) in a .txt file and want to load
this data exactly as is into a staging table where I hope to be able to
later get at selected columns using the SUBSTRING function. Here is my
target table:
CREATE TABLE JJA_BCP_NHO_DAT (
RecID int IDENTITY(1,1) NOT NULL
, Data VARCHAR(167) NOT NULL
)
Here is my bcp command:
bcp DevMDW.dbo.JJA_BCP_NHO_DAT in
N:RawData_MDSPurchaseUnzipArea2005_08_Aug_0149 7407.txt -f bcp.fmt
Here is the .fmt file:
8.0
1
1 SQLCHAR 0 167 "" 2
Data SQL_Latin1_General_CP1_CI_AS
Here are the first 3 lines of data in the .txt file (note how 1st 2
bytes are blank; last 2 bytes of each record are 78, 79, 80):
02160168C013CMA20050819 328
UUU AGAWAM 36422005072901001 BERKSHIRE
BANK 25013 78
01940155C001 MA20050805 254
UUU AGAWAM 28072005071801001 WEBSTER
BANK 25013 79
02350188C014CMA20050729 067
UUU AGAWAM 24812005070701001 FIRST
PIONEER FARM CR 25013 80
The bcp command runs OK but the output is "shifted" in the column
called DATA in the table. The IDENTITY column looks good in all rows,
but only the first row is OK in the DATA column. Starting in the 2nd
row, all bytes are truncated or shifted by what appears to be 2
characters. This is hard to show with pasting results but I ran this
query:
SET ROWCOUNT 10
SELECT RecID
, SUBSTRING(Data,1,10) as FirstTen
, SUBSTRING(Data,158,10) as LastTen
, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT
1 02160168 25013 78167
2 019401 25013 167
379 0235CR 2501167
43 80 01 CO 25167
5013 80 CORP 167
625013 80 REG SYS 167
7 25013 98ANK 167
8 25013 VGS BK 167
9 2501 21 MTG CO167
10RP 25RYWIDE HOM167
Every line in the .txt file ends after column 167 with x'0d0a'. I've
tried a field terminator of
and that produces this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered
in BCP data-file
Thanks in advance for some help on this.
View 2 Replies
ADVERTISEMENT
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Aug 3, 2006
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
View 5 Replies
View Related
Apr 2, 2007
Hi,
I have two tables table1 and new_table
Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .
And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.
This type of functionality is requirement for my project.
I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;
Please help me out to fix this issue
Thanks
Purnima
View 3 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 Replies
View Related
Nov 20, 2013
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
View 7 Replies
View Related
Jun 19, 2005
Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...
SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.
Is there any way to work around this?
Thanks for your help.
View 1 Replies
View Related
Sep 15, 2005
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
@PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
View 1 Replies
View Related
Mar 26, 2001
Having an identity column in target table, how do I use SQL 7 DTS ?
In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...
In DTS package, had Fast load and Enable Indenity checked - no luck !
What would be the standard procs for this since this sounds like a very common
senario.
Appreciate your help very much !
-Ivan
View 1 Replies
View Related
Jul 31, 2001
I'm sure this is a common problem but I can't find any relevant info on this site
I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.
e.g. a simplified version...
TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)
TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)
Runing the insert statement
INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2
results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?
thanks
neill
View 1 Replies
View Related
Jul 20, 2005
HI,I have an SQL Server table with only 1 column. That column is anidentity column. How can I insert a row in this table using SQLsyntax?I tried insertinto T_tableName () values ()and a few other options, but I can't seem to get it to insert.ThanksAlain
View 1 Replies
View Related
Jul 20, 2005
I've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer
View 2 Replies
View Related
Jun 2, 2006
In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table. The purpose is to get a list that looks like this after the insert:
RowID valueID1 348972 345223 94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable variable?
View 4 Replies
View Related
Jan 3, 2002
hi folks,
How to disable/enable an Identity Column in a table with the help of an sql
statement.
Thanks in advance.
Vineet Agarwal
View 1 Replies
View Related
Nov 13, 2000
Whenever I import (or even append from another table) data to a table that has an Identity column, I get an error: Cannot insert NULL values in Identity column.
Isn't the Idenity column supposed to incement automatically without me having to provide a value? Using INSERT provides an Identity value automatically, using import however doesn't.
How can I overcome this problem? (I now delete and recreate the Identity column - really BAD practice!)
Thank you.
View 4 Replies
View Related
Jul 13, 1999
I'm having difficulty bulk-copying a data file, using a format file, into a SQLServer 6.5 table with an identity column.
The data file is tab-delimited, and does not include the identity column (I want it to be automatically generated).
When I execute the following:
bcp DB1.dbo.WORK_TABLE in WorkTable.txt -Usa -P -fWorkTable.fmt -SSERVERX'
I receive this message:
Starting copy...
BCP copy in failed
I've had no problems loading a similar table without the identity column.
Can anybody help? Here is the table definition & bcp format:
CREATE TABLE WORK_TABLE
(
PersonID int NOT NULL IDENTITY (1,1)
,FirstName char(25) NULL
,MiddleName char(25) NULL
,LastName char(25) NULL
,Company char(50) NULL
,City char(25) NULL
,State char(2) NULL
,Phone char(25) NULL
)
-----
6.0
8
1 SQLCHAR 0 0 "" 1 PersonID
2 SQLCHAR 0 25 "" 2 FirstName
3 SQLCHAR 0 25 "" 3 MiddleName
4 SQLCHAR 0 25 "" 4 LastName
5 SQLCHAR 0 50 "" 5 Company
6 SQLCHAR 0 25 "" 6 City
7 SQLCHAR 0 2 "" 7 State
8 SQLCHAR 0 25 "
" 8 Phone
-----
View 2 Replies
View Related
Feb 8, 2007
Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.
Thanks in advance
Jamal"
View 6 Replies
View Related
Sep 2, 2006
I have a file I'm trying to do some non-set-based processing with. Inorder to make sure I keep the order of the results, I want to BULKINSERT into a temp table with an identity column. The spec says thatyou should be able to use either KEEPIDENTITY or KEEPNULLS, but I can'tget it to work. For once, I have full code - just add any file of yourchoice that doesn't have commas/tabs. :)Any suggestions, folks?--create table ##Holding_Tank ( full_record varchar(500)) -- thisworkscreate table ##Holding_Tank (id int identity(1,1) primary key,full_record varchar(500)) --that doesn't workBULK INSERT ##Holding_TankFROM "d: elnet_scriptspsaxresult.txt"WITH(TABLOCK,KEEPIDENTITY,KEEPNULLS,MAXERRORS = 0)select * from ##Holding_tank
View 2 Replies
View Related
Jul 20, 2005
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.tbl_appointment-------------------1. appt_id (pk) --- identity column, seed 25, increment 12. date_id3. time_start4. time_end5. appt_details6. lkp_emp_idPrivate Sub btnAddAppts_Click()On Error GoTo Err_btnAddAppts_ClickDim strsql As StringDoCmd.SetWarnings Falsestrsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"DoCmd.RunSQL strsqlDoCmd.SetWarnings TrueDoCmd.CloseExit_btnAddAppts_Click:Exit SubErr_btnAddAppts_Click:MsgBox Err.DescriptionResume Exit_btnAddAppts_ClickEnd Sub I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'. It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."
View 2 Replies
View Related
May 2, 2007
Hi All
Is it possible to insert the identity cloumn in a table via SSIS. I've an ID (Identity) column is a table and I am importing data in the table using Excel sheet. I want to insert the value of ID column as Identity(1,1).
View 10 Replies
View Related
Sep 11, 2006
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
thx
Kat
View 8 Replies
View Related
Dec 5, 2007
Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error?
Can anyone help???
Thanks!
View 3 Replies
View Related
Feb 6, 2008
Hi,
I need to insert data into a table using data flow task. Unfortunately this table's priamry key column (integer column) is not identity column. I am looking a way to get the primary key value for the new records. Please advice. Thanks
View 5 Replies
View Related
Feb 1, 2008
I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
+Identity Specification (Is Identity) Identity Increment Identity Seed
View 3 Replies
View Related
Mar 27, 2007
Hello,
I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.
So first 5 rows inserted get identity
1
2
3
4
5
If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.
So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.
Is there any way to avoid this?
Thanks,
John
View 3 Replies
View Related
Apr 22, 2008
Hi,
I am using data flow task to load data with the source as text file to sql server table with identity column.
After mapping all the columns except identity column, when I execute, the package failing saying
it can not insert null value into identity column. I donno how to get around with it ????
Thnaks,
V
View 6 Replies
View Related
Sep 3, 2015
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
View 4 Replies
View Related
Jun 10, 2015
how to add identity column into existing table in sql.
View 5 Replies
View Related
Dec 21, 2007
I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.although in my table I set IDENTITY_INSERT on (master Key)Please help me
View 5 Replies
View Related
Feb 5, 2006
Hello!
Do anybody know how to fix this error?
Cannot insert explicit value for identity column in table 'Gallery' when IDENTITY_INSERT is set to OFF.?
Thanks!
Varcar!
View 1 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Oct 4, 2007
I am trying to move data from Access to SQL Server 2000 using DTS.
I have an Access Source and SQL Server Desitination, My destination table has a field called tableID that is not in the source. TableID is a Primary Key and an Identity column.
I have Enable Identity Insert checked in the options of the Transform Data Task.
When I execute ythe task, I get the error
"Cannot insert the value NULL into column 'TableID'. Does not allow nulls. Insert Fails.
Does anyone know why this simple task would fail?
Mike
View 6 Replies
View Related
Apr 10, 2008
Code Snippet
Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click
Dim addclient As New newclient()
newclient.ClientID = 0
Dim result As DialogResult
result = addclient.ShowDialog
If result = DialogResult.OK Then
Dim Client As clients_and_sitesDataSet1.ClientsRow
Client = Clients_and_sitesDataSet1.Clients.NewClientsRow
Client.ClientID = addclient.ClientID
Client.Clientname = addclient.Clientname
Client.Street_and_number = addclient.Street_and_number
Client.Zipcode = addclient.Zipcode
Client.Place = addclient.Place
Client.Phone = addclient.Phone
Client.Email = addclient.Email
Client.Contact_person = addclient.Contact_person
Clients_and_sitesDataSet1.Clients.AddClientsRow(Client)
ClientsTableAdapter.Update(Client)------------------------------problem
MessageBox.Show("New Client is saved")
Else
MessageBox.Show("User cancelled operation")
End If
addclient = Nothing
End Sub
Code in VB (2005 express edition)
Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line.
when i try to run the program, i get an exeption
Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF
how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out.
And please give me specific guide lines on how to put in the code and code lines. I tried using
SET IDENTITY_INSERT Clients ON
But than I get all errors bout things not being declared, set not being supported anymore, things like that.
Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
View 10 Replies
View Related