Extract data in "Insert Into..." statement format

Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements, i.e.
if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], ... VALUES a,b,c)
insert into Table ([field1], [field2], ... VALUES d, e, f)
insert into Table ([field1], [field2], ... VALUES g, h, i)
insert into Table ([field1], [field2], ... VALUES j, k, l)
insert into Table ([field1], [field2], ... VALUES m, n, o)

Thanks in advanceVyas has just what you need:
http://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--|||INSERT INTO TABLE1(FIELD1,FIELD2)
SELECT ABC, XYZ from TABLE2 where bla bla

or

INSERT INTO TABLE1(A,B,C)
SELECT X,Y, 'some static text' FROM TABLE2

the number of columns must tally

Chad Richardson wrote:
> Is there a way in SQL Server 2000 to extract data from a table, such
that
> the result is a text file in the format of "Insert Into..."
statements, i.e.
> if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||Thanks all for the responses!

"Chad Richardson" <chad@.NIXSPAM_chadrichardson.com> wrote in message
news:1102ir7m0udi5cc@.corp.supernews.com...
> Is there a way in SQL Server 2000 to extract data from a table, such that
> the result is a text file in the format of "Insert Into..." statements,
> i.e. if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance

External columns from Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

External columns from a Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

Extensive Use pf Case Statement

Hi,

I want to generate a table output as per table a based on table b. In this case Range field os populated based on Shape and Process field values. You can see in both processes range is different. Now this is varies from shapes & processes which is stored in seperate master table which contains range ex. For Process_1 it is 0.5 & for Process_2 it is 0.10.

I have tried used case statement but I am unable to generate this dynamically in a single query. Can anyone Help me How can I achive this task?

Table A

Barcode

Shape

Process

Weight

Range

1

Shap_1

Proc_1

0.12

0.11-0.15

2

Shap_1

Proc_1

0.16

0.16-0.20

3

Shap_1

Proc_1

0.06

0.06-0.10

4

Shap_1

Proc_1

0.21

0.21-0.25

5

Shap_1

Proc_1

0.13

0.11-0.15

6

Shap_1

Proc_2

0.18

0.11-0.20

7

Shap_1

Proc_2

0.13

0.11-0.20

8

Shap_1

Proce_2

0.24

0.21-0.30

9

Shap_1

Proce_2

0.07

0.00-0.10

10

Shap_1

Proce_2

0.33

0.31-0.40

Table B

Barcode

Shape

Process

Weight

1

Shap_1

Proc_1

0.12

2

Shap_1

Proc_1

0.16

3

Shap_1

Proc_1

0.06

4

Shap_1

Proc_1

0.21

5

Shap_1

Proc_1

0.13

6

Shap_1

Proc_2

0.18

7

Shap_1

Proc_2

0.13

8

Shap_1

Proce_2

0.24

9

Shap_1

Proce_2

0.07

10

Shap_1

Proce_2

0.33

Nilkanth Desai

create table TableB(Barcode int, Shape char(6), Process varchar(8), Weight decimal(5,2))
insert into TableB(Barcode ,Shape , Process ,Weight)
select 1 , 'Shap_1', 'Proc_1' , 0.12 union all
select 2 , 'Shap_1', 'Proc_1' , 0.16 union all
select 3 , 'Shap_1', 'Proc_1' , 0.06 union all
select 4 , 'Shap_1', 'Proc_1' , 0.21 union all
select 5 , 'Shap_1', 'Proc_1' , 0.13 union all
select 6 , 'Shap_1', 'Proc_2' , 0.18 union all
select 7 , 'Shap_1', 'Proc_2' , 0.13 union all
select 8 , 'Shap_1', 'Proc_2', 0.24 union all
select 9 , 'Shap_1', 'Proc_2', 0.07 union all
select 10 , 'Shap_1', 'Proc_2', 0.33

create table Master(Process varchar(8), Range decimal(5,2))
insert into Master(Process, Range) values('Proc_1',0.05)
insert into Master(Process, Range) values('Proc_2',0.10)

select t.Barcode,
t.Shape,
t.Process,
t.Weight,
floor(t.Weight/m.Range)*m.Range+0.01 as RangeFrom,
floor(t.Weight/m.Range)*m.Range+m.Range as RangeTo
from TableB t
inner join Master m on m.Process=t.Process
order by t.Barcode

Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

Extended Properties

I am having some wierd behavior that I can't seem to correct regarding exteneded properties. Via a T-SQL statement, I have added several exteneded properties to a table, for example:

exec sp_addextendedproperty N'AP121ID', @.value = 'AP121 Primary Key',
@.level0type = N'Schema', @.level0name = dbo,
@.level1type = N'Table', @.level1name=ap121,
@.level2type = N'Column', @.level2name=AP121ID;
GO

This statement runs successfully. I can even query the sys.extended_properties table and see the entries.

However, if i look at the properties of teh table to which I added the exteneded properties, they are not listed. Nor does it allow me to add one via that properties page.

Any ideas?

Thanks...

Scott

Looks like you are adding extended properties to the column of the table and not the table itself.

Can you check for extended properties on the table column via the properties page ?

|||Ah, yep, that was it. Thanks!

Exporting SQL Server 2005 data into formatted XML file

Hello,

I currently have a stored procedure in my SQL Server 2005 database that contains a simple SELECT FOR XML statement. I would like to call this stored procedure in C# and use C# to write an XML file such that when I open it in Visual Studio for editing it looks properly nested (rather than all on one line). Is there a quick way to do this?

I would like the file to look something like the following when opened:

Code Snippet

<Element1>

<Element2>

<Element3>Text</Element3>

<Element4>Text</Element4>

</Element2>

</Element1>

Thanks.

Create a DataSet put your table in it and call the DataSet.ReadXML method, if you want formatting you can use a Repeater for Webform. Try the link below to get started.

http://msdn2.microsoft.com/en-us/library/360dye2a.aspx

|||Thanks for your reply! I will look into that.