creating scripts with data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

creating scripts with data

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-05-01 : 23:22:47

Hi friends
am creating database script to create database at client site.
am wondering is there any option export data also ,i mean we have some lookup tables so we want create insert statements where data is available.
i dont see any option in enterprise manager except to create script objects.
any ideas on this will b great.
Thanks

Cheers

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-05-02 : 02:25:59

Hi!!
i dont think you can create the insert statement using the Enterprise Manager.. for this either you can use the 3rd party tools where they give you the option to create the insert,update statements..
you can also use this store procedure to create the insert query ..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO

CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO

CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

----------------------------------

CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]

SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)

END

SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)

Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS

INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'

SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1

DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END

SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(3),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)

SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME

SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CreateInsQryEx "TableName","Column Names or *","Condition"--
1 st parameter is the tablename on which you want to create the insert table
2nd parameter is the column name.. of the table or you can specify the *
in the 3rd parameter not to use the where statment...

I hope This can help you..

Complicated things can be done by simple thinking

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-05-02 : 16:48:58

Thank u very much dude for sharing the script.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources