GENERATE INSERT QUERY

Question

Post reply

GENERATE INSERT QUERY

balaji.ganga

SSCrazy

Points: 2280
More actions
August 23, 2010 at 3:02 am

#74617

Hi All,
How can i generate an insert query script from sqlserver 2000 tables. Can anybody help me
Regards
Balaji.G

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

it means you know how to do it in 2005/2008. Why not use that database in SSMS instead of EM.

In EM, you can try this :-

1) Right Click on table and drag it to query analyzer.

2) It will give you the option to script for create,delete,alter.

I think this is your another weired question 😉 :w00t:

----------
Ashish

balaji.ganga SSCrazy Points: 2280 More actions · Answer 2

Hi Ashish

I'm asking about sqlserver 2000 production database..

crazy4sql SSCoach Points: 19590 More actions · Answer 3

unfortunately I dont have 2000.

but once you open query analyser and on your left if you open object browser, you will be able to do whatever I already suggested you referring as EM(Enter. Manaer which is in 2000)

----------
Ashish

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Failing that, would something like this do?

SELECT 'INSERT INTO yournewtable(Col1,Col2,Col3) SELECT '

+ Quotename(col1, '''') + ','

+ Quotename(col2, '''') + ','

+ Quotename(col3, '''') + ' UNION ALL'

FROM yourtable

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

balaji.ganga (8/26/2010)
Hi All,
How to generate a insert query script in sqlserver 2000. In Sql server 2000 don't have option to
generate a script.
Example
select empname,empid from employee
select 'insert into employee (empname,Empid) values('
empname +','+ empid ')' from employee
I need a following format output like
insert into employee(empname,empid) values('1000','Balaji') like that..
While i'm executing the above script. its getting error.
Syntax Error..
I would be greatly appreciated if any one help me out to solve this issue..
With Thanks & Regards
Balaji.G

You're missing a +

SELECT 'INSERT INTO employee (empname,empid) VALUES ('

+ empname + ',' + empid + ')'

FROM employee

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 6

Hi,

I dont think you can do this from sql server 2000.

If you have 2005 management studio then do the below to generate insert query

Right click on a table then choose --> SCRIPT TABLE AS --> INSERT TO

Thanks,

balaji.ganga SSCrazy Points: 2280 More actions · Answer 7

Hi,

I used + symbol in front of the column name.. its error occurred. Any other alternate solution is there? or else.. any tool is there?

Pls suggest me..

Thanks & Regards

Balaji.G

sivaj2k Ten Centuries Points: 1188 More actions · Answer 8

Hi

-- created sample table.

create table product

(cust varchar(25),

product varchar(20),

qty int)

-- inserting sample record

insert into product (cust,product,qty) values('KATE','SODA',6)

-- script to generate dynamic script

select 'insert into Product (cust,product,qty) values('+

''''+cust+'''' +','+''''+ product+''''+','+convert(varchar(10),qty)+')' from Product

it will works. if any issues reply.

Thanks

Siva Kumar J

balaji.ganga SSCrazy Points: 2280 More actions · Answer 9

Hi Siva,

select 'insert into sam(Id,Name,type,Email,Versionnumber)

values(' + ""id""+','+""Name""+','+ ""type""+','+""Email""+','+""Versionnumber""+ ')'

from sam

when i ran the above query.. the following error occurred

cannot use empty objects or column names.Use a single space if necessary..

then i removed one quotes of each column..

select 'insert into sam(Id,Name,type,Email,Versionnumber)

values(' + "id"+','+"Name"+','+ "type"+','+"Email"+','+"Versionnumber"+ ')'

from sam

Implicit conversion from one varchar to other varchar column cannot be performed..

because the collation of the value is unresolved.. due to collation conflict..

Then i used to sP_help sam

ColumnNameCollation

ID SQL_Latin_General_CP1_cI_AS

Name SQL_Latin_General_CP1_cI_AS

Type SQL_Latin_General_CP1_cI_AS

Version Latin_General_CP1_cI_AS

Email Latin_General_CP1_cI_AS

Could you pls suggest.. how to fix it.. because i want to build an insert statement scripts

for the most crucial objects.. then i insert those objects into Oracle environment..

Thanks & Regards

Balaji.G

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

balaji.ganga (8/31/2010)
Hi Siva,
select 'insert into sam(Id,Name,type,Email,Versionnumber)
values(' + ""id""+','+""Name""+','+ ""type""+','+""Email""+','+""Versionnumber""+ ')'
from sam
when i ran the above query.. the following error occurred
cannot use empty objects or column names.Use a single space if necessary..

What error occurred with this?

SELECT 'INSERT INTO sam(Id,Name,type,Email,Versionnumber) VALUES(' + id + ',' + name + ',' + TYPE + ',' + email + ',' +

versionnumber + ')'

FROM sam

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 11

Hi balagi,

Are you using stored procedure? or building insert script in other programmin language.

try this

1. declare variables.

2. set your values

3. insert

declare @Id int,@Name varchar(50),@type int,@Email varchar(100),@Versionnumber varchar(10)

--SET YOUR VALUES

SET @Name = 'Balaji.'

...

insert into sam(Id,Name,type,Email,Versionnumber)

values(@Id,@Name,@type,@Email,@Versionnumber)

Thanks,

Raj

sivaj2k Ten Centuries Points: 1188 More actions · Answer 12

Hi

don't use double codes. use single codes. You have not mentioned concatination symbol (+) for each field.

select 'insert into sam(Id,Name,type,Email,Versionnumber)

values(' + ''''+id+''+','+''''+Name+''''+','+ ''''+type+''''+','+''''+Email+''''+','+''''+Versionnumber+''''+ ')'

from sam

Thanks

Siva Kumar J

balaji.ganga SSCrazy Points: 2280 More actions · Answer 13

Dear Siva,

Thanks for your great and timely help.. Its working fine..

Thanks & Regards

Balaji.G