Problems importing data into sql server

I have a table for authors (for our bookstore) that has several fields
(firstname, lastname, etc.) and an author_id field (set as identity)

I'm trying to import a spreadsheet into this table, but keep getting
error messages that say I can't import data into the author_id field
(the identityf field).

Can someone suggest what I can do to overcome this?

Thanks,

Bill1) Import the data into a new flat table (ie: without indexes).
2) Run a report on the key field (identityf) -- there must be no duplicates.

select indentityf, count(*)
from newtable
group by identityf
having count(*)>1

should yield no results.

3) Correct the spreadsheet.
4) reimport the spreadsheet.

"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
> Thanks,
> Bill|||"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
<snip
Use the DTS. On the screen that allows you to select the tables, click the
Transform button. On this screem, you'll see 'Allow Identity Insert'.

BV
www.iheartmypond.comsql

Problems due to @@IDENTITY

I have a database that sits on SQL Server 2000 and the client is Access XP
connecting to SQL via ODBC.
The database has been in use since November 2003 and all has been working
well. However......over the past w a strange thing has been happening
that, when new customer details were entered and the record was saved, the
displayed record would change to that of another although the new record had
been saved to the table.
At first I thought it was Access playing around (bless it) but have since
discovered the true cause. Whenever a new customer is entered, a SQL
trigger fires that will also create a dummy record in another table
(tbl_MainCaseEntry) ready for the user to enter details. There is an
essential reason for the trigger but it is too long winded to explain why.
The trigger reads:
CREATE TRIGGER trg_NewCustomer
ON tbl_Customer
FOR INSERT
AS
BEGIN
DECLARE @.CustID INT, @.CaseCount INT
SET @.CustID = (SELECT CustomerID FROM Inserted)
INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
END
The field CustomerID in tbl_MainCaseEntry is the foreign key with the table
having it's own primary key of MainID (set as an identity field seeded
(1,1)).
I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
back the last identity field for the entire statement (in this case the
MainID in tbl_MainCaseEntry for the new record created by the trigger).
Is there any way that I can stop this from happening by using
IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced back
to this value? I have tried to use SET @.@.IDENTITY =
IDENT_CURRENT('tbl_Customer') but this does not work.
Any advice would be appreciated as this is driving me mad!!
Regards
DazzaUse SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||Have you tried using SCOPE_IDENTITY already?
Look it up in BOL to learn more.
-Jason
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||look up scope_identity in BOL
"Dazza" <Post2Group@.Only.com> wrote in message
news:%233683HKMFHA.1096@.tk2msftngp13.phx.gbl...
>I have a database that sits on SQL Server 2000 and the client is Access XP
>connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
> happening that, when new customer details were entered and the record was
> saved, the displayed record would change to that of another although the
> new record had been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
> table having it's own primary key of MainID (set as an identity field
> seeded (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
> back to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||I have looked in the BOL regards this feature but cannot understand it's
exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what is
the syntax please?
All BOL seems to show is that it will display the identity of the table
where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY].
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Use SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dazza" <Post2Group@.Only.com> wrote in message
> news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> happening
> had
> table
> back
>|||@.@.IDENTITY is fine to use within the trigger.
But if you want the calling code to return the identity value generated by
its INSERT statement (not the INSERT in the trigger), use SCOPE_IDENTITY()
in the calling code. http://www.aspfaq.com/2174
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#kky3OKMFHA.2384@.tk2msftngp13.phx.gbl...
> I have looked in the BOL regards this feature but cannot understand it's
> exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what
is
> the syntax please?
> All BOL seems to show is that it will display the identity of the table
> where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY()
AS
> [SCOPE_IDENTITY].
> Regards
> Dazza
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
documented.
working
record
since
pulling
>|||I also note another problem here.
I guess you assume that inserts always happen in singleton? You need this
code to be multi-row aware. For example, watch what happens when you do
this:
INSERT Customers(col1, ..., colN)
SELECT 'col1', ..., colN
UNION
SELECT 'col1', ..., colN
To correct this, your INSERT statement inside the trigger should simply be:
INSERT tbl_MainCaseEntry (CustomerID)
SELECT CustomerID FROM Inserted
Or, change the calling stored procedure to handle the logging part of this,
and eliminate the need for a trigger at all. You do control access to this
table via stored procedures, right?
In any case, the calling app can't expect to get back a single @.@.IDENTITY or
SCOPE_IDENTITY() in the multi-row insert case.
(I also think you should consider dropping the superfluous tbl_ prefix, but
that's just an opinion.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aaron
Many thanks for the advice.
I do not use SPs for the entry or retrieval of the data in forms as the
front-end uses linked tables. I have been working with SQL Server for about
a year (mostly admin) and still have a lot to learn regards development
methods for front-end access clients. The database in question is my first
real production development project but there are more to come I have been
told !!
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PMTcUKMFHA.2420@.TK2MSFTNGP12.phx.gbl...
>I also note another problem here.
>
> I guess you assume that inserts always happen in singleton? You need this
> code to be multi-row aware. For example, watch what happens when you do
> this:
> INSERT Customers(col1, ..., colN)
> SELECT 'col1', ..., colN
> UNION
> SELECT 'col1', ..., colN
> To correct this, your INSERT statement inside the trigger should simply
> be:
> INSERT tbl_MainCaseEntry (CustomerID)
> SELECT CustomerID FROM Inserted
> Or, change the calling stored procedure to handle the logging part of
> this,
> and eliminate the need for a trigger at all. You do control access to
> this
> table via stored procedures, right?
> In any case, the calling app can't expect to get back a single @.@.IDENTITY
> or
> SCOPE_IDENTITY() in the multi-row insert case.
> (I also think you should consider dropping the superfluous tbl_ prefix,
> but
> that's just an opinion.)
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>

Problematic Stored Procedures using Views with Triggers

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@.StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber

FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @.CompanyName VARCHAR(128)
DECLARE @.AddressID INT
DECLARE @.Street VARCHAR(256)
DECLARE @.City VARCHAR(128)
DECLARE @.StateID INT
DECLARE @.StateName VARCHAR(128)
DECLARE @.ZipCode INT
DECLARE @.Country VARCHAR(128)
DECLARE @.PhoneID INT
DECLARE @.PhoneNumber VARCHAR(32)
--DECLARE @.CompanyID INT

SELECT
--@.CompanyID = CompanyID,
@.CompanyName = CompanyName,
@.Street = Street,
@.City = City,
@.StateName = StateName,
@.ZipCode = ZipCode,
@.Country = Country,
@.PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@.StateName)

SET @.StateID = @.@.IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@.Street, @.City, @.StateID, @.ZipCode, @.Country)

SET @.AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@.PhoneNumber)

SET @.PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@.CompanyName, @.AddressID, @.PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@.CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @.CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@.CompanyName VARCHAR(128),
@.Street VARCHAR(256),
@.City VARCHAR(128),
@.StateName VARCHAR(128),
@.ZipCode VARCHAR(128),
@.Country VARCHAR(128),
@.PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@.CompanyName, @.Street, @.City, @.StateName,
@.ZipCode, @.Country, @.PhoneNumber)
END
GO

Hi,

first of all you have a misdesign in your trigger. Triggers occur per statement not per row, so inserting 50 rows in a table will fire the trigger only once not 50 times. In your case the trigger is only executed once which means that it would ingnore the 49 other inserted rows. I would first fix that and perhaps come back with the error line where the error you are describing occurs, marking it with something like -- <<-- Error occurs here.

HTH; Jens Suessmeyer.

http:/www.sqlserver2005.de

|||

I am only needing to add one company at a time. In testing the AddCompany stored procedure with Query Analyzer, it appears to work with the correct parameters. For example,

-- Insert First Company
EXEC AddCompany '', '', '', ...

-- Insert Second Company
EXEC AddCompany '', '', '', ...

With actual values, the records are inserted in the tables.

But, my delimma is in the AddCompany stored procedure I don't want to have to pass in a value for the identity column and I don't want to remove it from the view because I need it for the GetCompany stored procedure. And, I don't want to have to create a seperate view for each scenerio; so, my question is there anyway to achieve the desired goal.

I have been looking at the "check" and "no check" options, but I am not sure how they function.

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
You can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX
|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LXYou can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

Problem: Identity seed

I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LXYou can use IDENTITY_INSERT ON, load your data, turn it OFF and then reset
the seed to the max, using DBCC CHECKIDENT.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
I have an Identity field in a table with many records. Since the database is
corrupt, I recreated the table and loaded the original data back into the
new table.
I want to make sure the Identity field keep incrementing its value from the
max value instead of starting from 1 again.
Here is what I did:
Before I loading the data, I set the IDENTITY_INSERT ON and set it off when
the data was loaded. However, the new records' identity field value still
start
from the 1. :-(
I don't want to change the identity seed (it must be "1"). Is there any
other way I can achieve the goal?
Thanks a lot,
LX|||No. What you're trying to do is in violation of the use of an identity
column. Its sole purpose is to increment a value defined by the seed and
step. Identity_insert allows you to insert into an identity column, but the
system will do a check of the newly inserted and reset the seed if the new
value is larger than the current seed.
Bol has this thoroughly documented under 'set identity_insert'.
"FLX" <nospam@.hotmail.com> wrote in message
news:OTOc$g3jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> I have an Identity field in a table with many records. Since the database
is
> corrupt, I recreated the table and loaded the original data back into the
> new table.
> I want to make sure the Identity field keep incrementing its value from
the
> max value instead of starting from 1 again.
> Here is what I did:
> Before I loading the data, I set the IDENTITY_INSERT ON and set it off
when
> the data was loaded. However, the new records' identity field value still
> start
> from the 1. :-(
> I don't want to change the identity seed (it must be "1"). Is there any
> other way I can achieve the goal?
> Thanks a lot,
> LX
>

problem with updating identity in transactional replication

Hey. I've a few transactional publications. It fails with this error. When I
looked at the command, it's trying to insert a null in identity column. Why
does it do that? As of now, I've commented the update for the identity column
in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
identity column, what would happen? The reason I need the identity property
on subscriber side is because these replicated tables will be horizontally
partitioned and replicated to another server. And I'm hoping to use
Transactional Replication for that. Please let me know what should be done
and what's the ideal. thank you.
Cannot update identity column 'DeviceBeaconID'.
(Source: XIAN\XIAN2 (Data source); Error number: 8102)
{CALL sp_MSupd_logDeviceBeacon
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
Transaction sequence number and command ID of last execution batch are
0x002BA62A00000E7E000100000000 and 1.
This looks like you update proc as opposed to your insert proc.
Take the proc and open it up in a text editor. In the bottom half of the
proc comment out the part where it updates the identity column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:728B16DD-98F6-41F1-925F-53C6684FD831@.microsoft.com...
> Hey. I've a few transactional publications. It fails with this error. When
> I
> looked at the command, it's trying to insert a null in identity column.
> Why
> does it do that? As of now, I've commented the update for the identity
> column
> in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
> identity column, what would happen? The reason I need the identity
> property
> on subscriber side is because these replicated tables will be horizontally
> partitioned and replicated to another server. And I'm hoping to use
> Transactional Replication for that. Please let me know what should be done
> and what's the ideal. thank you.
>
> Cannot update identity column 'DeviceBeaconID'.
> (Source: XIAN\XIAN2 (Data source); Error number: 8102)
>
> {CALL sp_MSupd_logDeviceBeacon
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
> 15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.
|||Yes, I'm sorry. I used the wrong word. It's trying to update the Ident column
with a 'NULL' value. Can you explain what this statement does?
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
I think this is the line you have asked me to comment out. In my case, I
checked the @.bitmap variable and I found it to be '0x8009' when it does a
substring, it'll be case 0, right? so, it'll go to the else statement,
right?
Now, the question is why does the sp try to insert a null? Is it because no
modifications were made to it?
Thank you for your help.
Tejas
|||It should not be trying to do a null update. I am really confused here
however, you persist in talking about inserts, for the life of me it should
be an update. Or perhaps you are that pesky Paul Ibison in disguise trying
to push me over the edge?
The @.bitmap dictates which columns are to be updated. It looks like for your
bitmask the else will be used which means that the identity value will be
updated to the same value. A Null is passed due to the call type you are
using MCALL IIRC. As the identity value is not updated on the publisher no
value is passed - i.e. a NULL is passed. If the identity column was updated
(if this is possible) a value would be passed here instead of the null.
I think your update proc portion should look like this
update "datObjects" set
-- "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
-- "ObjectTypeID" end
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:2D828E4F-AC1A-4875-83CB-85B58FC80A73@.microsoft.com...
> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
> with a 'NULL' value. Can you explain what this statement does?
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> I think this is the line you have asked me to comment out. In my case, I
> checked the @.bitmap variable and I found it to be '0x8009' when it does a
> substring, it'll be case 0, right? so, it'll go to the else statement,
> right?
> Now, the question is why does the sp try to insert a null? Is it because
> no
> modifications were made to it?
> Thank you for your help.
> Tejas
|||lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm sorry
again for using the word insert in my first post.Thank you very much for your
help. This was what I was actually looking for. Thank you.
|||Ok, thanks Paul.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:50263252-D63C-448A-8A89-470CABA983EA@.microsoft.com...
> lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm
> sorry
> again for using the word insert in my first post.Thank you very much for
> your
> help. This was what I was actually looking for. Thank you.
|||Hey Hilary. I could not do what you had asked. I'm pasting the whole
sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
Below, objectId is the identity column. But it doesn't exist in the else part.
I've pasted the sp as is. Made no modifications to it. So, if i comment the
objectid in the if part it works fine... Is it normal to be not there in the
else part? because that's where u asked me to comment it out.
-----
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else "ObjectID"
end
,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
|||Ok Paul, I think this is it
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
--on the safe side I will do this too
--"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"ObjectID"
--end
--,
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
--"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
--"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
>
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5FA239EC-5A40-4D2F-AB2F-570BF4B6FAF9@.microsoft.com...
> Hey Hilary. I could not do what you had asked. I'm pasting the whole
> sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
> Below, objectId is the identity column. But it doesn't exist in the else
> part.
> I've pasted the sp as is. Made no modifications to it. So, if i comment
> the
> objectid in the if part it works fine... Is it normal to be not there in
> the
> else part? because that's where u asked me to comment it out.
> -----
> CREATE procedure "sp_MSupd_datObjects"
> @.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
> bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
> bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
> ,@.bitmap binary(3)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "datObjects" set
> "ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "ObjectID"
> end
> ,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> ,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end,
> "ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> GO
>
|||Just wanted to know if you noticed that the commented lines in the sp are two
different fields...
In the if, it's objectid
in the else, it's objectTypeID.
These are two different columns with no correlation...
Is the commenting still correct?
Just want to be sure.
Thank you,
TEJAS
(not Paul)
|||Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:561E41D4-279E-4FF3-B976-8F75A2AA7689@.microsoft.com...
> Just wanted to know if you noticed that the commented lines in the sp are
> two
> different fields...
> In the if, it's objectid
> in the else, it's objectTypeID.
> These are two different columns with no correlation...
> Is the commenting still correct?
> Just want to be sure.
> Thank you,
> TEJAS
> (not Paul)

Problem with Update query

Hi - I have 2 tables, which I would like to 'merge'.
tblAccess and tblCars
tblAccess:
a_id int (identity)
a_user (varchar50)
a_pass (varchar20)
a_cid (bigint)
tblCars:
c_id (identity)
c_user (varchar50)
c_carname (varchar50)
These were joined using the a_cid to c_id field, but now I want to copy
the tblAccess.a_user into the relative record in the tblCars.c_user
table.
Something like:
Update tblCars set tblCars.c_user = (select tblAccess.a_user from
tblAccess WHERE tblAccess.a_id = tblCars.c_id)
But I get an error advising the subquery returns more than 1 result.
Could anyone please help?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***>> But I get an error advising the subquery returns more than 1 result.
The error message suggests that you have more than one value for a_id in the
tblAccess table for each value of c_id in the tblCars table.
If you want to get the right value, you might have to look carefully into
the logic you use in the WHERE clause in your subquery. If all you want to
do is to avoid the error, then you can use an extrema aggregate like:
UPDATE tblCars
SET c_user = ( SELECT MAX( tblAccess.a_user )
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
WHERE EXISTS ( SELECT *
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
The WHERE clause is to constrain the statement to update only the values
that have matching values for the identifier columns in both the tables.
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I guess that you meant to post something like this, assume that you
know that IDENTITY is not ever a key , how to do proper data element
names:and followed industry standards. And not put "rbl-" suffixes on
table names or table anem suffixes on column names.
This is still bad, but it is not awful:
CREATE TABLE VehicleAccess
(vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
vehicle_pass VARCHAR (50) NOT NULL,
vin CHAR(17) NOT NULL --industry std
REFERENCES Cars(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (vehicle_user, vehicle_pass) -- wild guess by me
);
CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY, --industry std
vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
car_name VARCHAR (50) NOT NULL -- you name your car?
);
First of all, fields are not anything like columns and rows are not
anything like records! No wonder you were using the word "merge" in
this posting -- you are locked into a file system model of data, not an
RDBMS.
Next, a data element has one and only one name in a schema. It does
not change from table to table. But file systems do not have a data
dictionary, so you missed this basic point.
Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.
Of course; this design has no data integrity. You cannot trust
anything you get out of it. This attempt at kludging an unusable
design only showed you a FEW of the problems you have.
Start over. What are the attributes of a car? The VIN is the natural
key. But why does a car have a name? Does it come when you call it?
And even if it have a name, why is it soooooo long?
You need a table of vehicle users (they are entities, aren't they?
Model them!).
You need a table of vehicle assignments (or access rights) It will
reference the Cars and the Users, but also have a date range, the users
role (driver, passenger), etc.
You used over-sized columns -- Why did you pick BIGINT and VARCHAR(50)?
They will only accumulate garbage. You have no DRI actions. YOu have
no constraint.
Oh, and remember to do a full data audit to clean up what you have in
the DBMS now.