May 14, 2008 at 4:40 am
Hi. I have attempted to copy data from Table A & Table B to Table A & Table B from Database 1 to Database 2 using SET IDENTITY_INSERT.
SQL returned an error:
Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'Default'.
[Code]
SET IDENTITY_INSERT MyDatabase2.dbo.MyTable ON
INSERT INTO [MyDatabase2].[dbo].[MyTable]
([Field1]
,[Field1]
,[Default])
SELECT
Field1,
Field2,
Default
FROM [MyDatabase1].[dbo].[MyTable]
SET IDENTITY_INSERT MyDatabase.dbo.MyTable OFF
[/Code]
Table Structure:
Table B.
The 'Default' Field 'Data Type' = nvarchar(4000) and 'allow nulls' is deselected (i.e. NULL not allowed).
Table B in Database 1 has values for field 'Default' for some records but not for all. Where there is no value rather than there be NULL it is just blank.
However Table A in Database 1 has values for all records (10,0,50,40 etc....)
If I specify a value for 'Default' on INSERT i.e. '40' tables migrates OK, 40 is then entered as a value in 'Default' for all records (not what I want).
Question: How can I migrate these fields between databases?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 14, 2008 at 4:56 am
You have to wrap Default in select part of the query in [] brackets as it is reserved word
Piotr
...and your only reply is slàinte mhath
May 14, 2008 at 8:04 am
I will give that a try the next time I need to migrate.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply