BULK INSERT messes up IDENTITY value

I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeralda
Ok, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
> in after restoring the database anew and truncate that table. The identity
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda

BULK INSERT messes up IDENTITY value

I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeraldaOk, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to cop
y
> in after restoring the database anew and truncate that table. The identit
y
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda

bulk insert (again and again..)

Hi !
sorry to bother you again with that topic..but...
Bulk Insert inserts null value when it finds null string in my source
file to load...
is there any way to prevent this'
I would like to get null fields instead of NULL value in my base..
thanks again :)
++
VinceHi
You can do a post update of
UPDATE TABLE
SET Col1 = NULLIF('NULL')
or you should look at the source try to generate the file differently.
John
"Vince .>" <vincent@.<remove> wrote in message
news:38qk4150iaddn8ha1k7njss01466u2u8kv@.
4ax.com...
> Hi !
> sorry to bother you again with that topic..but...
> Bulk Insert inserts null value when it finds null string in my source
> file to load...
> is there any way to prevent this'
> I would like to get null fields instead of NULL value in my base..
>
> thanks again :)
> ++
> Vince

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...
> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA
.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
KeyExt Key
150K
273J
375K
460A
Table 2
KeyValue
50KABC
60ADEF
75KGHI
Current Join on SMC
Key ExtKey Value
150KABC
273J(null)
375KGHI
460ADEF
Desired Join result
Key ExtKey Value
150KABC
273JOther
375KGHI
460ADEF
I've also tried updating the view but that didn't work for me either.
TIA
Bill
Bill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building a table from SQL Query

I am hoping someone can point me in the right direction with this.

I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:

==============================
Column Name || Column Value
------||------
Column Name || Column Value
------||------

I hope I have explained myself properly. Any help would be greatly appreciated.

Do you want to transpose your data from row to column?

If this is what you want, it depends on which version of SQL server you are using. In SQL Server 2005, there is a PIVOT function which does this knid of job. In 2000, you can use CASE statement to construct a customized solution.

If you need help on this, you can post some sample data for others to look at. Plus the final result you are expecting.

|||Unless I misunderstood, PIVOT is not quite what I am looking for.

What I need is to take the name of the column that a field is in and use that as a header for table.

Any help would be greatly appreciated.

GKC|||

Could you test this out: (instead of PIVOT, use UNPIVOT)

step 1:

create table usingunpivot2005
(
myID varchar(10) primary key,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50)
)
insert into usingunpivot2005 (myID, col1, col2, col3)
values('myid01','40','20','30')

Step 2:

with switchCTE as(
select cast(colname as varchar(5)) as colname, value
from usingunpivot2005 p

UNPIVOT
(value for colname in (col1, col2, col3)) as unpvt)
select *
from switchCTE

You should know your columns' name. If the value in that column is NULL, that column will be skipped.

|||SELECT ColName, MIN(CASE WHEN P.myID = 'myid01' THEN col1 END) AS Value
FROM
(SELECT 'Col 1' as ColName, myID, col1 FROM usingunpivot2005
UNION
SELECT 'Col 2', myID, col2 FROM usingunpivot2005
UNION
SELECT 'Col 3', myID, col3 FROM usingunpivot2005) P
GROUP BY ColName
ORDER BY ColName

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/